Sql-server – Multiple SELECT vs single SELECT over TCIP

selectsql servertcpip

My question is a performance one:

I need to select roughly 400+ rows, i know their ids.

currently i loop in code and fire off multiple queries over tcp/ip – because its easy:

...
select [columns] where id = 1544;
select [columns] where id = 4765;
select [columns] where id = 3544;
select [columns] where id = 4090;
select [columns] where id = 5060;
...

Would it be more efficient on SQL server to fire off a single query for a date range instead (the rows I'm after are date-sequential, but we currently don't have a date field in that table), keeping in mind the overhead of multiple transactions vs a single transaction:

select [columns] where date between x AND y;

Best Answer

The round trip time from the client to the server per query starts to add up with your approach. Even if it is only 1ms per query, you are burning almost half a second for no gain. Combine the statements into one. At the very least submit them as a single batch and process the individual results sets in the application.

Related Solutions

Sql-server – How to query transfers for single source to single destination

I don't know all of your source data (or why there isn't any type of unique constraint that would prevent full-on duplicates or a source with multiple destinations), but given only the sample data supplied:

;WITH s AS 
(
  -- first let's eliminate duplicates
  SELECT DISTINCT Source, Destination 
    FROM dbo.MyTable
)
SELECT Source, Destination
FROM s
WHERE NOT EXISTS
(
  SELECT 1 FROM s AS d WHERE 

  -- eliminate chains in either direction:
    d.Destination = s.Source OR d.Source = s.Destination

  -- eliminate any source with multiple destinations:
    OR (d.Source = s.Source AND d.Destination <> s.Destination)

  -- eliminate any destination with more than one source
    OR (d.Destination = s.Destination AND d.Source <> s.Source)
);

SQL fiddle demo

Sql-server – How to update ID=null values in table to incremental counter values

You can do this.

WITH T
     AS (SELECT ISNULL((SELECT MAX(ID) FROM StagingTable), 0) + 
                    ROW_NUMBER() OVER (ORDER BY NaturalID) AS New_ID,
                ID
         FROM   StagingTable
         WHERE  ID IS NULL)
UPDATE T
SET    ID = New_ID

So the windowed function is used in the SELECT list but you can still use the result of it to UPDATE the column.

You should probably have a filtered index unique constraint on ID WHERE ID IS NOT NULL to prevent duplicates too. Or run this at serializable isolation level to block concurrent inserts.

Best Answer

Related Solutions

Sql-server – How to query transfers for single source to single destination

Sql-server – How to update ID=null values in table to incremental counter values

Related Question