Re: Creating indexes on cdata and cglobal tables?
Hi Dave (or anyone), Another quick follow up (about clustered vs nonclustered indexes) indexing the CDATA table. You mentioned creating an index on CDATA like the following (ie a clustered index).CREATE UNIQUE CLUSTERED INDEX idxCDATA ON cdata (cfid, app) It turns out that in some cases we already have an index on our client dbs as follows (ie a non-clustered index):- ---CREATE UNIQUE NONCLUSTERED INDEX [id1] ON [dbo].[CDATA] ([cfid] ASC,[app] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]--- - Do you think that changing the index on this table to clustered from nonclustered would provide us with performance improvements? Thanks again! Nick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354886 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating indexes on cdata and cglobal tables?
Do you think that changing the index on this table to clustered from nonclustered would provide us with performance improvements? Well, queries that use a clustered index generally run significantly faster than queries that use a nonclustered index. If you only put one index on a table, it's usually a clustered index because of this. If you put more than one index on a table, you have to decide where to use your clustered index, as you can only have one clustered index per table - a clustered index matches the physical arrangement of table rows. So, yes. But, I don't know whether the performance improvements you'd get from faster queries would be counterbalanced by the degradation you might see with inserts and deletes. Those can end up slower than they would otherwise be. Finally, honestly, if you already have an index in place, that's probably good enough. It's unlikely that you have enough records in this table that dropping one index and creating another is going to make much of a difference. The best thing you can do to improve performance of Client variables is to disable global Client variable updates if you don't need them. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354891 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Creating indexes on cdata and cglobal tables?
Hi Folks, We use client variables in our client databases and I've seen some information that you can get better performance by creating indexes on those tables. For instance this page (http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=sharedVar s_08.html) says the following:To improve performance, you should also create indexes when you create these tables. For the CDATA table, index these cfid and app columns. For the CGLOBAL table, index the cfid column. Has anyone on this list created indexes for these tables before? If so, any suggestions of the best way to do this? For instance, would we need to add an identity column to these tables first? If so, how best to do that given that they are in use with quite a bit of data already. Then, what would the actual index code look like? Many, many thanks in advance. Nick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354837 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating indexes on cdata and cglobal tables?
We use client variables in our client databases and I've seen some information that you can get better performance by creating indexes on those tables. For instance this page (http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=sharedVar s_08.html) says the following:To improve performance, you should also create indexes when you create these tables. For the CDATA table, index these cfid and app columns. For the CGLOBAL table, index the cfid column. Has anyone on this list created indexes for these tables before? If so, any suggestions of the best way to do this? For instance, would we need to add an identity column to these tables first? If so, how best to do that given that they are in use with quite a bit of data already. Then, what would the actual index code look like? You don't need to create an identity column, the tables already have a perfectly usable natural key. Just do exactly what it says. Also, disable global client variable updates in the CF Administrator if you're not using them. If you're using SQL Server, you'd have something like this: CREATE UNIQUE CLUSTERED INDEX idxCDATA ON cdata (cfid, app) Or you could just use the handy SQL Server management GUI. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354838 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating indexes on cdata and cglobal tables?
Thanks Dave. A quick follow up. We are only keeping data in these tables for 1-3 days, and it's obviously changing a lot as the site is used. So, does that mean that once the indexes are created, they should be re-indexed / rebuilt frequently (because of the frequent changes in the data)? Nick Return-Path: listmas...@houseoffusion.com Received: from mail.houseoffusion.com [64.118.74.225] by mail67.safesecureweb.com with SMTP; Tue, 5 Mar 2013 16:42:07 -0500 To: cf-talk cf-talk@houseoffusion.com Message-ID: CACi=XSZ+BG0xk1LEnP5jkMmJCDXMkjkvmtNG4=retrbvon5...@mail.gmail.com Subject: Re: Creating indexes on cdata and cglobal tables? References: 588834a5$200fd16e$31331ae6$@com Date: Tue, 5 Mar 2013 16:41:57 -0500 Precedence: bulk Reply-To: cf-talk@houseoffusion.com From: Dave Watts dwa...@figleaf.com MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Rcpt-To: n.glea...@citysoft.com X-SmarterMail-Spam: SpamAssassin 0 [raw: 0], SPF_None, DK_None X-SmarterMail-TotalSpamWeight: 0 We use client variables in our client databases and I've seen some information that you can get better performance by creating indexes on those tables. For instance this page (http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=sharedVar s_08.html) says the following:To improve performance, you should also create indexes when you create these tables. For the CDATA table, index these cfid and app columns. For the CGLOBAL table, index the cfid column. Has anyone on this list created indexes for these tables before? If so, any suggestions of the best way to do this? For instance, would we need to add an identity column to these tables first? If so, how best to do that given that they are in use with quite a bit of data already. Then, what would the actual index code look like? You don't need to create an identity column, the tables already have a perfectly usable natural key. Just do exactly what it says. Also, disable global client variable updates in the CF Administrator if you're not using them. If you're using SQL Server, you'd have something like this: CREATE UNIQUE CLUSTERED INDEX idxCDATA ON cdata (cfid, app) Or you could just use the handy SQL Server management GUI. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354840 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating indexes on cdata and cglobal tables?
A quick follow up. We are only keeping data in these tables for 1-3 days, and it's obviously changing a lot as the site is used. So, does that mean that once the indexes are created, they should be re-indexed / rebuilt frequently (because of the frequent changes in the data)? No, the database will do that for you automatically. INSERTs, UPDATEs and DELETEs may take a bit longer with indexes as a result, but SELECTs will be faster if they filter by key columns. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354842 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating indexes on cdata and cglobal tables?
Indexes can become fragmented over time, however. If you add the indexes and note degradation of performance after a while, then you may need to look at rebuilding indexes and statistics. If you are using SQL Server, there are tools built-in to set up automated maintenance plans which can include index rebuilding. -Carl V. On 3/5/2013 2:25 PM, Dave Watts wrote: No, the database will do that for you automatically. INSERTs, UPDATEs and DELETEs may take a bit longer with indexes as a result, but SELECTs will be faster if they filter by key columns. Dave Watts, CTO, Fig Leaf Software ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354844 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating indexes on cdata and cglobal tables?
You may also see a slight performance boost if you change the data fields from text to varchar(max) if you are on sql 2005+, plus storage size is better. Pretty sure this was ok to do with CF8 plus. Also be mindful of how much data you put into the client scope. Less the better, since every request will do a select and update of all data in the db regardless of any variables being updated in a request. Not sure if this is different with 10 since, we no longer use client, and have opted for sticky sessions in our cluster. I had a junior guy put a large wddx string into the client scope once (before we did better code reviews) and the instant the code was deployed killed our cluster. Byron Mann Lead Engineer Architect HostMySite.com On Mar 5, 2013 4:23 PM, Nick Gleason n.glea...@citysoft.com wrote: Hi Folks, We use client variables in our client databases and I've seen some information that you can get better performance by creating indexes on those tables. For instance this page ( http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=sharedVar s_08.html) says the following:To improve performance, you should also create indexes when you create these tables. For the CDATA table, index these cfid and app columns. For the CGLOBAL table, index the cfid column. Has anyone on this list created indexes for these tables before? If so, any suggestions of the best way to do this? For instance, would we need to add an identity column to these tables first? If so, how best to do that given that they are in use with quite a bit of data already. Then, what would the actual index code look like? Many, many thanks in advance. Nick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354849 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
