Help Needed in SQL Query

Question

Dear Friend, Below is my SQL Query which run against 30000000 rows and its takes 6 minutes to run indexes are defiened to the all columns used in the where clause as wll as inner join Please help me

SELECT auditData.id,nstmp.ProviderMaster_ID as CDRComment,Auditdata.Calltypetag  
from    Auditdata AuditData
inner join NoSeriesMaster_temp nstmp  on nstmp.NosereisTemp like '91%'
where  Auditdata.id in (select id from auditdata_temp1 where tatcalltype is  null)  
    and AuditData.CallTolen=12 and  Auditdata.Callto like nstmp.NosereisTemp + '%' and       AuditData.AuditMaster_ID=74

thanx in advance

Answer 1

Your query is very intensive, bsesides you iterate a big table, you do a join and a nested query.

Maybe you can create a view with the result of the nested sql. In any case you must rewwrite the query without using that complexity.

Another solution could be considering to partition the big table into slices or aggregate the data using some OLAP methods.

Which Database Engine are you using?

Answer 2

You've got LIKE expressions in both your WHERE clause (like nstmp.NosereisTemp + '%') and your JOIN ON clause (like '91%'). That's always going to be slower than using a direct comparison, and I think it may also affect whether the indexes you have can be used efficiently.

Would it be possible to modify the tables to include a field that you could use to join/filter on? For example, could you pre-compute the like '91%' and store the value in the table?

Answer 3

It's going to be doing, at best, index scans as you are doing LIKE condition checks (won't be able to do an index seek which is generally the best for performance). Not much you can do about that - just check the execution plan, keep an eye out for table scans which will need looking at (missing index)

Possibly changing the "Auditdata.id IN" clause to an EXISTS condition may perform better (I'm assuming id is the PK in auditdata_temp1 and so there won't be multiple with the same value, in which case EXISTS won't make a huge difference, if any).

With that volume of data, you may want to consider partitioning your data, which you can do from SQL 2005 onwards, but you need Enterprise Edition so may not be an option. See here for info.

Covering indexes - may be you would get better performance with one.

Really, we need to see the execution plan which may throw other things into the mix.

Answer 4

The sub-query

Firstly get rid of the sub-query and use a join instead, like this:

SELECT 
auditData.id, nstmp.ProviderMaster_ID as CDRComment, Auditdata.Calltypetag  

FROM Auditdata AuditData

INNER JOIN NoSeriesMaster_temp nstmp  
ON Auditdata.Callto like nstmp.NosereisTemp + '%' 
AND nstmp.NosereisTemp like '91%'

INNER JOIN auditdata_temp1 adt
ON Auditdata.id = adt.id
AND adt.tatcalltype is  null

WHERE AuditData.CallTolen = 12
AND AuditData.AuditMaster_ID = 74

That will help a bit.

The join using a like clause

1. This will mess-up your execution plan as the optimiser can not calculate the best search path as the value changes run-time.
2. It's a text search which will be evaluated for every row of AuditData... not good!

Solution

Add a bit column to NoSeriesMaster and update it on schedule for non-updated entries to 1 Where NosereisTemp like '91%'. Use this bit value in your query instead.

Look at changing this:

Auditdata.Callto like nstmp.NosereisTemp + '%'

Using a similar concept. It's difficult to say exactly how without knowing your data.

Answer 5

You should shift around the JOIN ON / WHERE criteria a little, the join criteria should be the predicate that relates the two tables:

INNER JOIN NoSeriesMaster_temp nstmp
ON Auditdata.Callto like nstmp.NosereisTemp + '%

You then need to move the following predicate into the WHERE section of your query:

WHERE nstmp.NosereisTemp like '91%'

This might help SQL server come up with a more sensible execution plan.

If this doesnt help then you should look into pre-calculating the value of nstmp.NosereisTemp like '91%' - SQL Server should be able to handle queries of this kind perfectly fine, however it may have an impact on JOINS

More than that its not possible to say without an execution plan, however I can say for sure that it is NOT your subquery! :-) (Feel free to try rewriting it as a JOIN, however I'll be extremely surprised if removing the subquery is what fixes your problem)