TI - SQL Query with JOIN or View, Slow to Execute

[michaelm]

Hi,

I have a process that needs to run a SQL query that contains a JOIN. The query is simple enough:


SELECT *
FROM TableA AS a
INNER JOIN
TableB AS b
ON a.OrderNumber = b.DocNumber AND a.SKUKey = b.SKUKey
AND a.DateKey >= '20111002' AND a.DateKey <= '20111105'


If I run this query in SQL Server Manager it returns about 4,000 rows of data in 4 seconds. However if I run this TI Process it takes about 6,000 seconds. I have taken out all the code in the Data and Metadata tabs for testing purposes, so this Process is only executing one line of code which is counter = counter + 1; ASCIIoutput counter. I left this in so the process has something to do.

My understanding is that the TI Process will simply pass the query on so it should not matter what is in the query as long as SQL can do it. I have created a view using this query and referenced the view in the TI with no effect. However if I create a new table using the above query and reference this new table, the process executes in a couple of seconds.

Any ideas ?

Thanks.

[jim wood]

There are several things that could be at fault here:

1) The version of the ODBC driver you are using.
2) The query settings for teh user used in teh ODBC connection.
3) The indexing of the 2 tables you are trying to join.
4) Network issuess

Have you looked in to these? Have you tried running the same query through likes of toad or something similar?

Jim.

[jim wood]

Another point is that it runs the query when it opens to show you the preview. How long does this take?

The TI process will run through every row of the result applying calculations. If you have code in both metadata and data it will do this twice.

I hope that helps,

Jim.

[michaelm]

Thanks for your responses Jim. I found the problem to be the "Use Unicode" setting in the Data Source of the TI. By disabling this the process runs in a matter of seconds.
-Michael

[jim wood]

Nice one. I've had a problem with that one in the past. For me it stopped the query from running at all. A bit of a strange one me thinks??