SQL query runs into a timeout on a sparse dataset

Question

For sync purposes, I am trying to get a subset of the existing objects in a table.

The table has two fields, [Group] and Member, which are both stringified Guids.

All rows together may be to large to fit into a datatable; I already encountered an OutOfMemory exception. But I have to check that everything I need right now is in the datatable. So I take the Guids I want to check (they come in chunks of 1000), and query only for the related objects.

So, instead of filling my datatable once with all

SELECT * FROM Group_Membership

I am running the following SQL query against my SQL database to get related objects for one thousand Guids at a time:

SELECT *
FROM Group_Membership
WHERE
    [Group] IN (@Guid0, @Guid1, @Guid2, @Guid3, @Guid4, @Guid5, ..., @Guid999)

The table in question now contains a total of 142 entries, and the query already times out (CommandTimeout = 30 seconds). On other tables, which are not as sparsely populated, similar queries don't time out.

Could someone shed some light on the logic of SQL Server and whether/how I could hint it into the right direction?

I already tried to add a nonclustered index on the column Group, but it didn't help.

maybe you can add an identity field to the table and use that to fetch records by the thousadns ? ..where MyField < 1000 — GuidoG, Aug 23 '17 at 13:52

Tim Biegeleisen · Answer 1 · 2017-08-23T13:46:36.830

1

I'm not sure that WHERE IN will be able to maximally use an index on [Group], or if at all. However, if you had a second table containing the GUID values, and furthermore if that column had an index, then a join might perform very fast.

Create a temporary table for the GUIDs and populate it:

CREATE TABLE #Guids (
    Guid varchar(255)
)

INSERT INTO #Guids (Guid)
VALUES
    (@Guid0, @Guid1, @Guid2, @Guid3, @Guid4, ...)

CREATE INDEX Idx_Guid ON #Guids (Guid);

Now try rephrasing your current query using a join instead of a WHERE IN (...):

SELECT *
FROM Group_Membership t1
INNER JOIN #Guids t2
    ON t1.[Group] = t2.Guid;

As a disclaimer, if this doesn't improve the performance, it could be because your table has low cardinality. In such a case, an index might not be very effective.

edited Aug 23 '17 at 13:46

answered Aug 23 '17 at 13:36

Tim Biegeleisen

502,043
27
286
360

I am right now trying to find [how to use a temporary table to load a DataTable](https://stackoverflow.com/questions/36501877/sql-server-connection-context-using-temporary-table-cannot-be-used-in-stored-pro), but this seems a bit complicated... – Alexander Aug 23 '17 at 13:58
@Alexander It's only complicated because maybe you haven't worked much with temp tables before...their syntax isn't too different from regular tables, except they get erased when your session ends. – Tim Biegeleisen Aug 23 '17 at 13:58
I have just tried to run the same query in SSMS to check whether the temp table bring any improvement; but the original query also took only a few milliseconds in SSMS. How can a `SqlCommand` time out when the same query, run via SSMS, returns in a few milliseconds? – Alexander Aug 23 '17 at 14:12
I don't know...perhaps there is a lot of data going out of SQL Server and this is the cause of the delay – Tim Biegeleisen Aug 23 '17 at 14:14

SQL query runs into a timeout on a sparse dataset

1 Answers1