Solved

Query too slow comparing strings

Posted on 2013-01-24
3
320 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 45

Accepted Solution

by:
Kent Olsen 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

631 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