Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a list of 3000 strings, and I am passing them (twenty at a time) into a parametrized IN clause. It definitely isn't getting the results I would like to see ~ 500ms per execution.

The column is an index. Do you know a better way than this:

SELECT * FROM [ohb].[dbo].[MasterUrls] WITH (NOLOCK) WHERE Hash 
IN(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19)

A list of 3000 takes between 3 and 5minutes. I really need that down to around 30 seconds. Is this possible?

I am using MSSQL 2008 R2 on a server with 24 gigs of RAM, and dual 8core NUMA Xeons @2.4Ghz running on a 6HDD (@15k/rpm) RAID 10 ISCSI.

The table has 1.4M rows, and the index is a non-clustered index.

Execution plan shows the index scan as 90% of the total execution.

share|improve this question
    
are using a stored procedure? is the Hash column has unique non-clustered index, in other words is Hash column has unique values? what is the Hash column's datatype? is it uniqueidentifier? –  Coder Hawk Feb 19 '11 at 6:39
1  
What schema and indexes please? –  gbn Feb 19 '11 at 10:24
3  
NOLOCK is not SQL's turbo button –  Eric Humphrey - lotsahelp Feb 20 '11 at 22:40
    
No but in a table where there isn't a second that a transaction isn't happening, nolock keeps you from deadlocking. –  Jeremy Boyd Jun 9 '11 at 15:48
1  
nolock helps only in select statements..which usually are not involved in deadlocks. Please read the following questions on our site: What are the main causes of deadlock and how to prevent? and Justify NOT using (nolock) hint in every query. You might get some answers to your questions. –  Marian Jun 9 '11 at 20:27
add comment

3 Answers 3

SELECT * will invalidate any optimal use of an index (it isn't covering) even if hash is indexed. Your index scan is most likely on the clustered index because of this.

Personally, I'd start with

  • putting the 3000 search values into a table with an index
  • Edit: as per Marian's comment, this can be passed in a list or table already
  • use this in any of JOIN, IN, EXISTS (same plan usually)
  • ensure my index on MasterUrls suits using Hash and covers col1, col2, col3

Something like

CREATE TABLE #foo (Hash ...)
INSERT #foo...
CREATE INDEX IX_FOO ON #foo (hash)

--either
CREATE NONCLUSTERED INDEX IX_Hash ON MasterUrls (hash) INCLUDE (col1, col2, col3)
--or    
CREATE CLUSTERED INDEX IXC_Hash ON MasterUrls (hash)

SELECT col1, col2, col3
FROM [ohb].[dbo].[MasterUrls] M
JOIN
#foo F ON M.Hash = F.Hash
share|improve this answer
3  
I'd add to this that if you need to send the list of params from the application you can send it as a string that you can split in pieces, using a function, or as a table variable (I see you're using SQL 2008, so it's ok) and then continue with gbn's idea. –  Marian Feb 19 '11 at 10:54
add comment

Pass the values in via a table valued paramater. This way they are already in table form. Then copy the values from the TVP into a temp table, which has a clustered index on it. Use this temp table as a JOIN member of your query.

Remove the SELECT * and change it to only the columns that you need, with the additional columns included. If SELECT * is needed then include all additional columns as a included columns within the index.

share|improve this answer
    
    
Yep, that would be related. –  mrdenny Sep 29 '11 at 20:17
add comment
up vote -1 down vote accepted

I actually solved this in a MUCH faster SQL-less fashion.

In another step before this one, I grab the URLs and IDs from the table (instead of just the URL, which i would hash for the lookup -- this question), save them in memory, then on the FS (in case memory failed -- async, of course), then when it came time to do the lookup I read from my data I stored in memory/FS.

The process now takes less than 5 seconds on average to do a lookup and update (the step after this question) the data for 3000 rows. Much better than 240 seconds on avg.

share|improve this answer
1  
So instead of filtering the data in SQL you do the same operations in memory? This means you have the data in memory, on the FS (for memory failures, of course) and also in the database (just for backup..or why?). Are you sure this is the right way to go for performance issues? –  Marian Jun 7 '11 at 14:27
1  
@Marian, Where time is a factor, no one was able to get the SQL statement itself down to less than 1 minute for 3000 rows. We hired Microsoft, we hired $400/hr SQL consulting firms no one could do it with SQL. So I removed SQL from the equation and it worked. Faster and cheaper than anyone else. –  Jeremy Boyd Jun 8 '11 at 14:19
2  
I understand the frustration. That's why I think you should've better listen to Sandy's and GBN's questions and help us helping you by providing the table's schema and maybe some sample data. I'm sure there's a better solution to this problem. You just need to provide us with more information. By providing only the Select statement we can't say more than we just already said. –  Marian Jun 9 '11 at 20:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.