T-SQL query timeout / performance issue

Firstly add an index if there isn't one, on table_a.uid, but i assume there is.

Some alternate queries to try,

select newkey 
from fn_split
left outer join table_a
on newkey = uid
where uid IS NULL


select newkey 
from fn_split(blah)
where newkey not in (select uid 
                     from table_a)

select newkey 
from fn_split(blah) f
where not exists(select uid 
                 from table_a a 
                 where f.newkey = a.uid)

There is plenty of info around here as to why you should not use a Guid for your primary key, especially if it in unordered. That would be the first thing to fix. As far as your query goes you might try what Paul or Tim suggested, but as far as I know EXCEPT and NOT IN will use the same execution plan, though the OUTER JOIN may be more efficint in some cases.

If you're using MS SQL 2008 then you can/should use TableValue Parameters. Essentially you'd send in your guids in the form of a DataTable to your stored procedure.

Then inside your stored procedure you can use the parameters as a "table" and do a join or EXCEPT or what have you to get your results.

This method is faster than using a function to split because functions in MS SQL server are really slow.

But I guess is the time is being taken due to massive Disk I/O this query requires. Since you're searching on your UId column and since they are "random" no index is going to help here. The engine will have to resort to a table scan. Which means you'll need some serious Disk I/O performance to get the results in "good time".

Using the Uid data type as in index is not recommended. However, it may not make a difference in your case. But let me ask you this:

The guids that you send in from your app, are in just a random list of guids or is here some business relationship or entity relationship here? It's possible, that your data model is not correct for what you are trying to do. So how do you determine what guids you have to search on?

However, for argument sake, let's assume your guids are just a random selection then there is no index that is really being used since the database engine will have to do a table scan to pick out each of the required guids/records from the million records you have. In a situation like this the only way to speed things up is at the physical database level, that is how your data is physically stored on the hard drives etc.

For example:

1. Having faster drives will improve performance

2. If this kind of query is being fired over and over then more memory on the box will help because the engine can cache the data in memory and it won't need to do physical reads

3. If you partition your table then the engine can parallelize the the seek operation and get you results faster.

4. If your table contains a lot of other fields that you don't always need, then spliting the table in two tables where table1 contains the guid and the bare minimum set of fields and table2 contains the rest will speed up the query quite a bit due to the disk I/O demands being less

5. Lot's of other things to look at here

Also note that when you send in adhoc SQL statements that don't have parameters the engine has to create a plan each time you execute it. In this case it's not a big deal but keep in mind that each plan will be cached in memory thus pushing out any data that might have been cached.

Lastly you can always increase the commandTimeOut property in this case to get past the timeout issues.

How much time does it take now and what kind of improvement are you looking to get ot hoping to get?

If I understand your question correctly, in your client code you have a comma-delimited string of (string) GUIDs. These GUIDS are usable by the client only if they don't already exist in TableA. Could you invoke a SP which creates a temporary table on the server containing the potentially usable GUIDS, and then do this:

        select guid from #myTempTable as temp
        where not exists
           (
            select uid from TABLEA where uid = temp.guid
            )

You could pass your string of GUIDS to the SP; it would populate the temp table using your function; and then return an ADO.NET DataTable to the client. This should be very easy to test before you even bother to write the SP.

I am questioning what you do with this information.

If you insert the keys into this table afterwards you could simply try to insert them on first hand - that's much faster and more solid in a multi-user environment then query first insert later:

create procedure TryToInsert @GUID uniqueidentifier, @Name varchar(n) as
begin try
    insert into Table_A (UID,Name)
    values (@GUID, @Name);
    return 0;
end try
begin catch
    return 1;
end;

In all cases you can split the KeyList at the client to get faster results - and you could query the keys that are not valid:

select  UID
from    Table_A
where   UID in ('new guid','new guid',...);

If the GUID are random you should use newsequentialid() with you clustered primary key:

create table Table_A (
    UID uniqueidentifier default newsequentialid() primary key,
    Name varchar(n) not null
);

With this you can insert and query your newly inserted data in one step:

insert into Table_A (Name)
output inserted.*
values (@Name);

... just my two cents

In any case, are not GUIDs intrinsically engineered to be, for all intents and purposes, unique? (i.e. universally unique -- doesn't matter where generated). I wouldn't even bother to do the test beforehand; just insert your row with the GUID PK and if the insert should fail, discard the GUID. But it should not fail, unless these are not truly GUIDs.

http://en.wikipedia.org/wiki/GUID

http://msdn.microsoft.com/en-us/library/ms190215.aspx

It seems you are doing a lot of unnecessary work, but perhaps I don't grasp your application requirement.