How to make query faster when finding valuelist in query?

Question

I have two tables which are in two different servers. both tables are huge they have more than 100k records. what im trying to get all the account numbers that are not in bitdisclosure table but in member table where member_flags=1 The problem is that is really slow how can i make it faster?

 <cfquery datasource="#v_DATASOURCE_2#" name="GetMember" result="tmpResult2">
 SELECT [Account] FROM [BITDisclosure]  
  </cfquery>  
  <cfquery datasource="#v_DATASOURCE#" name="GetPositiveCOSAccounts" result="tmpResult"> 
    SELECT        member_account       ,member_flags      
    FROM [member]   where member_flags=1   and   member_account not IN (#ValueList(GetMember.account)#)
     </cfquery>

When i try it with top 1000 record it gives results fast, is there another way i can speed up the process?

try left join instead of in, also are there proper indices on the searching columns on both tables — radar, Sep 28 '14 at 03:28
If you can't do linked servers, have the first query generate an XML string that represents all the accounts. On the second one have it shred that xml into a table variable and do a select in on that — James A Mohler, Sep 28 '14 at 21:55
yes there are indices on the columns, and no i havent thought about linked servers, never done it — jfishbow, Sep 28 '14 at 22:53

score 4 · Answer 1 · edited May 23 '17 at 12:13

This is going to take some reading and experimenting, but I've been dealing with multiple data sources for over a decade. Linked servers sounds like a good idea, but in practice, you're dealing with network limitations and other bottlenecks. Passing data around as XML gets easier the more you do it. You'll find that using structured XML in this manner is far easier than dealing with lists. As your list gets larger, you'll eventually hit a size limit in SQL Server and then you're stuck.

Change your first query to

SELECT [Account] FROM [BITDisclosure] AS XML

This will return the full record set as XML directly from SQL Server, removing the overhead of converting the record set into XML using CF.

Now you can pass this result as an argument (variable) to your second query. There are a number of XML query functions built into SQL Server. Take a look at this answer on SO for some examples. That answer links to this article, which covers a lot of the basics.

For example:

create table #demo (field1 xml)
insert into #demo (field1) 
values ('<document>
<header>Alphabet
<items>
<item id="a">a is for apple
<item id="b">b is for balloon
</items>
</document>')

You can then run this SQL statement to get back the contents of the <header> node.

select field1.query('/document/header') from #demo

In your case, you might change your 2nd query into a stored procedure, where you can pass the XML data in as a parameter, @accountXML. Then you can query @accountXML directly instead of putting the data into a temp table as shown in the example.

score 0 · Answer 2 · answered Sep 29 '14 at 12:37

0

Why not shove all of the data into one of the queries into a temp query using queryNew? http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f94.html

answered Sep 29 '14 at 12:37

user3324834

59
4

user4092484 · Answer 3 · 2014-09-29T19:51:39.510

Not sure why you'd need member_flags if member_flags=1 is a condition of your query, but here are a couple of suggestions for speeding up that query:

SELECT member_account, member_flags
FROM member
LEFT OUTER JOIN BITDisclosure
ON member.member_account = BITDisclosure.account
WHERE member.member_flags=1

OR (if there are multiple records in BITDisclosure with the same value for the account field)

SELECT member_account, member_flags
FROM member
LEFT OUTER JOIN (SELECT distinct BITDisclosure.account from BITDisclosure) as BD
ON member.member_account = BD.account
WHERE member.member_flags=1

the last clause (where member.member_flags=1) will reduce the number of matches it has to make with the joining table (BITDisclosure). and on the second version, it will also reduce the number of records pulled for comparison.

How to make query faster when finding valuelist in query?

3 Answers3