Liveware Publishing Support Forum

I can run the following code from the MS SQL Studio and it runs to completion in about a minute but when it is executed from RRWRUN it can take hours. The report does print the correct output so everything is running from both systems which eliminates incorrect login, SQL logic, etc. Any advice on the speed difference would be greatly appreciated. Basically, this query simply lists all an item^s information that was in the previous table not in the current table (deleted items).____select distinct __ cast([previous].upc as char(15)) UPC,__ cast([print].name as char(8)) Name,__ cast([item].desc6 as char(60)) Desc6,__ cast([item].desc3 as char(12)) Desc3,__ cast([item].desc7 as char(12)) Desc7,__ [item].price__from previous,__ current,__ print,__ item__where [print].key=[current].key__ and [previous].key=[current].prevkey__ and [previous].upc=[item].upc__ and [previous].upc not in__ (select [current].upc__ from current, print__ where [current].key=[print].key)__order by UPC____Thank you!__Pat Quick

Is this a user sql report?____Kathleen__R&R Support

Yes. I believe so. It works correctly and outputs the right information, just takes way too long.____Thank you for your help!

select distinct __ cast([previous].productupc as char(15)) UPC,__ cast([print].planogramname as char(8)) PLNName,__ cast([item].productdesc6 as char(60)) Desc6,__ cast([item].productdesc3 as char(12)) Desc3,__ cast([item].productdesc7 as char(12)) Desc7,__ [item].productprice__from previous,__ current,__ print,__ item__where [print].dbkey=[current].dbkey__ and [previous].dbkey=[current].prevdbkey__ and [previous].productupc=[item].productupc__ and [previous].productupc not in__ (select [current].productupc__ from current, print__ where [current].dbkey=[print].dbkey)__order by UPC

I believe it is a user report. Not sure why half the query dropped but here is the same one with "." replaced with "^".____select distinct __ cast([previous]^productupc as char(15)) UPC,__ cast([print]^name as char(8)) Name,__ cast([item]^productdesc6 as char(60)) Desc6,__ cast([item]^productdesc3 as char(12)) Desc3,__ cast([item]^productdesc7 as char(12)) Desc7,__ [item]^productprice__from previous,__ current,__ print,__ item__where [print]^dbkey=[current]^dbkey__ and [previous]^dbkey=[current]^versionkey__ and [previous]^productupc=[item]^productupc__ and [previous]^productupc not in__ (select [current]^productupc__ from current, print__ where [current]^dbkey=[print]^dbkey)__order by UPC

OK. The posting is still messing with the brackets. I have now replaced "[" and "]" with "{" and "}", respectively.____select distinct __ cast({previous}.productupc as char(15)) UPC,__ cast({print}.name as char(8)) Name,__ cast({item}.productdesc6 as char(60)) Desc6,__ cast({item}.productdesc3 as char(12)) Desc3,__ cast({item}.productdesc7 as char(12)) Desc7,__ {item}.productprice__from previous,__ current,__ print,__ item__where {print}.dbkey={current}.dbkey__ and {previous}.dbkey={current}.versionkey__ and {previous}.productupc={item}.productupc__ and {previous}.productupc not in__ (select {current}.productupc__ from current, print__ where {current}.dbkey={print}.dbkey)__order by UPC

Can you create a view table on the server that does this select and then report on the view?____Kathleen__R&R Support

The view table will not work in this case because the table links change at every run. We run through a list of external database links, pull in what we need, run the report on that specific data set and then move on to the next database link in the list. Do you have any other suggestions? This is really becoming a deadline issue as the list continues to grow with that particular client.____Thank you!__Pat Quick

I will work on running the command line SQL script that would create the table in the interum. The issue here is that is has to be able to run from our software that loops through the list.____Thanks!

Alright. I got the SQL script running correctly in our software that creates the table view as suggested. I have also editted the Report Writer report so that it uses the view instead of the raw tables. The results, while being correct, show no appreciable difference in speed. I can manually run the SQL statement from the studio and it reflects exactly what I have seen running the report vs. running the SQL. On a side note, there is no speed difference on the SQL statement from the studio vs. the same statement from the table view.____Thank you again for your help...__Pat Quick