SQL Select from OLEDB locks QV Desktop

[#1]

I have one OLEDB data source that is a view on an MS SQL Server 2008 box that is off host. My script sets up the connection and makes the first query just fine returning 15,000 rows. The second query into a new QV table returns about 1,500,000 rows but then just seems to hang. I tried commenting out the second query and the document reloads just fine. If I comment out the first query the reload hangs and I have to task manager kill QV even after waiting 30 minutes. I tried a debug reload of 10, 100, and 1000 rows and all fail when the second query is made.

As you can see from the log file, the script actually gets past the large load with no problems but hangs right at the end almost like it is trying to close the connection to the SQL Server or somethign and timing out???

I'm attaching a copy of my script and the output log file. Is there something I'm missing with this query? This is my first attempt at querying SQL Server for data versus CSV files.

I'm on an i7, 12GB RAM, running QV11 SR1 64bit on Windows 7 64bit.

Thanks

Rick

[rwunderlich · Accepted solution]

What you are experiencing is "death by synthetic key". Those two tables have many fields in common. QV is spending a llot of resource building the synthetic key combinations at the end of script.

Looking at your script, my first choice would be to concatenate both queries into a single QV table. Right after the ClosedBilling: label insert the line:

CONCATENATE (OpenBilling) LOAD *;

-Rob

http://robwunderlich.com

[#3]

And the winner is....Rob Wunderlich.  Seriously, thanks Rob. Is I was tearing down the SQL to just a few fields I was able to get it to run and then I saw the Synthetic keys and realized what was happening. I'm in the process of concatenating now.

Muchas Gracias

Rick