sproc cs_Tags_Get Needs to be addressed!

I have 10k+ galleries in my CS install... one for each user. 
The problem I am seeing now is when I try to load up the /photos/default.aspx
page I am getting SQL timeout errors.

I was able to trace it down to  

cs_Tags_Get 
    @SettingsID=1000
    ,@SectionQuerySQL='xxx' and IsActive = 1'
    ,@CategoryList=''; 

The problem in this is I have 10K+ sections being passed into the
SectionQuerySQL parameter!
i.e. SELECT SectionID from cs_Sections WHERE SectionID IN
(3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,...n)
where n > 10,000 

Any suggestions?

you can try forcing the use of an index on SectionID.

From: http://www.sql-server-performance.com/transact_sql.asp 

If you find that SQL Server uses a TABLE SCAN instead of an INDEX SEEK when you
use an IN or OR clause as part of your WHERE clause, even when those columns are
covered by an index, consider using an index hint to force the Query Optimizer
to use the index.

For example:

SELECT * FROM tblTaskProcesses WHERE nextprocess = 1 AND processid IN (8,32,45)

takes about 3 seconds, while: 

 SELECT * FROM tblTaskProcesses (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND
processid IN (8,32,45)

returns in under a second. [7.0, 2000] Updated 6-21-2004 Contributed by David
Ames

I will give that a shot tonight, however, even if it does work for me it still
needs to be addressed in the core code.

I would suggest that the SectionIDs be passed as a delimited ntext parameter and
looped through to create a temptable.  Then it's just a matter of INNER JOINing
to the temptable of SectionIDs. It might be worth a try.

 http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-integers

iso3200:

you can try forcing the use of an index on SectionID.

From: http://www.sql-server-performance.com/transact_sql.asp 

If you find that SQL Server uses a TABLE SCAN instead of an INDEX SEEK when you
use an IN or OR clause as part of your WHERE clause, even when those columns are
covered by an index, consider using an index hint to force the Query Optimizer
to use the index.

For example:

SELECT * FROM tblTaskProcesses WHERE nextprocess = 1 AND processid IN (8,32,45)

takes about 3 seconds, while: 

 SELECT * FROM tblTaskProcesses (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND
processid IN (8,32,45)

returns in under a second. [7.0, 2000] Updated 6-21-2004 Contributed by David
Ames

 



This shaved off 3 seconds. It is still timing out.