6

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.

  • 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? – CoderHawk Feb 19 '11 at 6:39
  • 1
    What schema and indexes please? – gbn Feb 19 '11 at 10:24
7

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
  • 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
3

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.

1

Yes:- This is possible just you have to store that all passing id into one string with comma separated value and make one function :

Just Follow the step :

First Make one function:

 ALTER FUNCTION [dbo].[UDF_IDListToTable]
 (
    @list          [varchar](MAX),
    @Seperator     CHAR(1)
  )
 RETURNS @tbl TABLE (ID INT)
 WITH 

 EXECUTE AS CALLER
 AS
  BEGIN
    DECLARE @position INT
    DECLARE @NewLine CHAR(2) 
    DECLARE @no INT
    SET @NewLine = CHAR(13) + CHAR(10)

    IF CHARINDEX(@Seperator, @list) = 0
    BEGIN
    INSERT INTO @tbl
    VALUES
      (
        @list
      )
END
ELSE
BEGIN
    SET @position = 1
    SET @list = @list + @Seperator
    WHILE CHARINDEX(@Seperator, @list, @position) <> 0
    BEGIN
        SELECT @no = SUBSTRING(
                   @list,
                   @position,
                   CHARINDEX(@Seperator, @list, @position) - @position
               )

        IF @no <> ''
            INSERT INTO @tbl
            VALUES
              (
                @no
              )

        SET @position = CHARINDEX(@Seperator, @list, @position) + 1
    END
END
RETURN
END

after that you just use with inner join :-

SELECT  *
FROM    [ohb].[dbo].[MasterUrls] AS mul WITH ( NOLOCK )
        INNER JOIN dbo.UDF_IDListToTable(@IDString, ',') udtl ON mul.hash = udtl.ID; 
-1

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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