Solved

Query too slow comparing strings

Posted on 2013-01-24
3
315 Views
Last Modified: 2013-01-28
Hello, I have a query that returns a table, checking whether the id of the main table exists in some others... The problem i think is that this id is a guid (varchar(36)), and this "IN" statement for a 36 length string could be to blame for the slow query.

SELECT "FALSE" AS HitMeXTremeSelected, HitMeXTremeRegisterID AS MHitMeXTremeRegisterID,HitMeXTremeNewRegisterID AS MHitMeXTremeNewRegisterID,
HitMeXTremeClosed AS MHitMeXTremeClosed,HitMeXTremeExcluded AS MHitMeXTremeExcluded,HitMeXTremeImportDate AS MHitMeXTremeImportDate,
hit9e7663f93614481a9a01d6e0d70b71aa AS Mhit9e7663f93614481a9a01d6e0d70b71aa,hit7846a637579e441286d824baaa619c63 AS Mhit7846a637579e441286d824baaa619c63,
hitbd5da3f8c5f1418194643a62db4abbf7 AS Mhitbd5da3f8c5f1418194643a62db4abbf7,hit7c45226d7d594edb83574254d739f2e2 AS Mhit7c45226d7d594edb83574254d739f2e2,
hit5f6e65a503464ef7965ef091b015d2ba AS Mhit5f6e65a503464ef7965ef091b015d2ba,hit07bbbc69ccc94d3a9e8a3c70a3f4e682 AS Mhit07bbbc69ccc94d3a9e8a3c70a3f4e682,
hit2dd2849c721e43df8271eb6dbfaa9a3a AS Mhit2dd2849c721e43df8271eb6dbfaa9a3a,hit42c22f7eca7643e9b01c583f52330e12 AS Mhit42c22f7eca7643e9b01c583f52330e12,
hit9fc32e9c62384854904ed4ccb8a41585 AS Mhit9fc32e9c62384854904ed4ccb8a41585,hit722b407b32124e6ca01b184dd934c4bc AS Mhit722b407b32124e6ca01b184dd934c4bc,
hit77906e7151944f0597465631a3f1b47a AS Mhit77906e7151944f0597465631a3f1b47a,hit0cabe711e80b479a958aba2ffea353d6 AS Mhit0cabe711e80b479a958aba2ffea353d6,
hitdec626278ab941ea852b0428e914cf22 AS Mhitdec626278ab941ea852b0428e914cf22,hit21007e6d45cc4515862a9b2b93b8d976 AS Mhit21007e6d45cc4515862a9b2b93b8d976,
hit425b903eb494443794b06bde1609d69a AS Mhit425b903eb494443794b06bde1609d69a,hitcd678699985841dfae592320f35b7a04 AS Mhitcd678699985841dfae592320f35b7a04,
hit380ab1155a46465d9ba326b6ee78131d AS Mhit380ab1155a46465d9ba326b6ee78131d 
FROM tbldb01745c33005641a4817fea2e246f2b26 
WHERE HitMeXTremeNewRegisterID = "" 
AND HitMeXTremeRegisterID NOT IN (SELECT id_MasterRegister FROM dbHitData.tblMatched 
				WHERE id_MasterRegister IS NOT NULL 
				AND id_Match NOT IN (SELECT id_Match FROM dbHitData.tblRejected) 
				AND id_Mirror IN (SELECT id_Mirror FROM dbHitSystem.tblMirrors WHERE id_Procedure = '63e926ab-735c-411a-afea-92e573088a34'))  
AND HitMeXTremeRegisterID NOT IN (SELECT id_Register FROM dbHitData.tblExcluded WHERE id_Procedure = '63e926ab-735c-411a-afea-92e573088a34')

Open in new window


I know the basics of SQL, but is there any way I can improve the performance of this query??

Thanks
0
Comment
Question by:toddinho
3 Comments
 
LVL 45

Accepted Solution

by:
Kdo earned 300 total points
ID: 38816197
Hi Todd,

Can you tell me a bit about the data?

If the number of rows where HitMeXTremeNewTegisterID="" is small, selecting those rows and outer joining the other tables to it should be very fast.   Other data patterns may do better with different SQL.

And of course, the proper indexes are critical.


Kent
0
 
LVL 29

Assisted Solution

by:fibo
fibo earned 200 total points
ID: 38824409
selecting "not in" is usually slow, and string comparisons may be quite slow too.

as suggested, have you indexed these string fields? this might improve things a lot...
0
 

Author Comment

by:toddinho
ID: 38827026
Yeah, indexing the columns was the way to go... Thanks for the quick answers!!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question