Query taking too long to run (1 Viewer)

[jobrien4]

Please help optimize my query.

I'm creating a report that shows usage and sales of inventory over the past 90 days. In addition, I need to report to list the Last Activity Date for each product.

I have a query to give me all activity - CSP IPs. This takes about 5 seconds to run. To filter that to only activity within last 90 days for queries that total sales and usage over this period, I have another query:

Select * From [CSP IPs] Where ([CSP IPs].[Date] > (Date()-90));

This query takes 10 to 15 minutes to run. Even if I add that date criteria to the date field in the [CSP IPs] query itself, it goes from 5 second to much much longer.

Why does adding that one Date criteria make the query take so much longer to run?

Here is the SQL on the [CSP IPs] query

SELECT informix_injitd_rec.itd_cmpy_id AS Company, informix_injitd_rec.itd_actvy_dt AS [Date], informix_injitd_rec.itd_bgt_for AS BoughtFor, informix_injitd_rec.itd_bgt_for_id AS Customer, informix_injitd_rec.itd_ref_pfx AS IPPfx, informix_injitd_rec.itd_ref_no AS IPNum, informix_injitd_rec.itd_ref_itm AS IPItem, informix_injitd_rec.itd_ref_sbitm AS IPSubItem, informix_injitd_rec.itd_trs_seq_no AS TransSeq, informix_injitv_rec.itv_trs_wgt AS Quantity, informix_injitd_rec.itd_frm AS Form, informix_injitd_rec.itd_grd AS Grade, informix_injitd_rec.itd_size AS [Size], informix_injitd_rec.itd_fnsh AS Finish, informix_injitd_rec.itd_wdth AS Width, informix_injitd_rec.itd_lgth AS Length, informix_ipjsoh_rec.soh_ord_pfx AS SOPfx, informix_ipjsoh_rec.soh_ord_no AS [Order], informix_ipjsoh_rec.soh_ord_itm AS OrderItem, informix_ipjsoh_rec.soh_ord_sitm AS OrderRelease, informix_sahstn_rec.stn_tot_val AS [Value], informix_sahstn_rec.stn_npft_avg_val AS NetProfit

FROM informix_inrcsp_rec INNER JOIN ((((informix_injitd_rec INNER JOIN informix_injitv_rec ON (informix_injitd_rec.itd_trs_seq_no = informix_injitv_rec.itv_trs_seq_no) AND (informix_injitd_rec.itd_actvy_dt = informix_injitv_rec.itv_actvy_dt) AND (informix_injitd_rec.itd_ref_sbitm = informix_injitv_rec.itv_ref_sbitm) AND (informix_injitd_rec.itd_ref_itm = informix_injitv_rec.itv_ref_itm) AND (informix_injitd_rec.itd_ref_no = informix_injitv_rec.itv_ref_no) AND (informix_injitd_rec.itd_ref_pfx = informix_injitv_rec.itv_ref_pfx) AND (informix_injitd_rec.itd_cmpy_id = informix_injitv_rec.itv_cmpy_id)) INNER JOIN informix_ipjtrh_rec ON (informix_injitv_rec.itv_actvy_dt = informix_ipjtrh_rec.trh_actvy_dt) AND (informix_injitv_rec.itv_ref_sbitm = informix_ipjtrh_rec.trh_ref_sbitm) AND (informix_injitv_rec.itv_ref_itm = informix_ipjtrh_rec.trh_ref_itm) AND (informix_injitv_rec.itv_ref_no = informix_ipjtrh_rec.trh_ref_no) AND (informix_injitv_rec.itv_ref_pfx = informix_ipjtrh_rec.trh_ref_pfx) AND (informix_injitv_rec.itv_cmpy_id = informix_ipjtrh_rec.trh_cmpy_id)) INNER JOIN informix_ipjsoh_rec ON (informix_ipjtrh_rec.trh_jbs_no = informix_ipjsoh_rec.soh_jbs_no) AND (informix_ipjtrh_rec.trh_jbs_pfx = informix_ipjsoh_rec.soh_jbs_pfx) AND (informix_ipjtrh_rec.trh_cmpy_id = informix_ipjsoh_rec.soh_cmpy_id)) INNER JOIN informix_sahstn_rec ON (informix_ipjsoh_rec.soh_ord_sitm = informix_sahstn_rec.stn_ord_rls_no) AND (informix_ipjsoh_rec.soh_ord_itm = informix_sahstn_rec.stn_ord_itm) AND (informix_ipjsoh_rec.soh_ord_no = informix_sahstn_rec.stn_ord_no) AND (informix_ipjsoh_rec.soh_ord_pfx = informix_sahstn_rec.stn_ord_pfx) AND (informix_ipjsoh_rec.soh_cmpy_id = informix_sahstn_rec.stn_cmpy_id)) ON (informix_inrcsp_rec.csp_bgt_for_cus_id = informix_sahstn_rec.stn_sld_cus_id) AND (informix_inrcsp_rec.csp_bgt_for_cus_id = informix_injitd_rec.itd_bgt_for_id) AND (informix_inrcsp_rec.csp_std_lgth = informix_injitd_rec.itd_lgth) AND (informix_inrcsp_rec.csp_std_wdth = informix_injitd_rec.itd_wdth) AND (informix_inrcsp_rec.csp_fnsh = informix_injitd_rec.itd_fnsh) AND (informix_inrcsp_rec.csp_size = informix_injitd_rec.itd_size) AND (informix_inrcsp_rec.csp_grd = informix_injitd_rec.itd_grd) AND (informix_inrcsp_rec.csp_frm = informix_injitd_rec.itd_frm) AND (informix_inrcsp_rec.csp_cmpy_id = informix_injitd_rec.itd_cmpy_id)

WHERE (((informix_injitd_rec.itd_cmpy_id)="OBS") AND ((informix_injitd_rec.itd_bgt_for)="C") AND ((informix_injitd_rec.itd_ref_pfx)="IP") AND ((informix_injitv_rec.itv_trs_wgt)<0) AND (Not (informix_inrcsp_rec.csp_bgt_for_cus_id) Is Null) AND ((informix_inrcsp_rec.csp_gnrt_std_prd) Like "0") AND ((informix_injitd_rec.itd_ord_ffm)=" 0000000000000"));

 

[Galaxiom]

I suspect the base query is also slow but it is showing you results as soon as some are available. (To test, put an order clause on the query and see if it still shows results in five seconds.)

Add indexes to the fields in the table that are used as criteria in the query. This will usually solve the problem.

At a glance, you appear to have an inordinate number of complex joins in the query. It makes me wonder about the suitabliity of the data structure. (The repetitive object naming and lack of presentation makes the query unreadable so I have not looked closely.)

The Like "0" should be replaece by an equals test.

 

[DavidAtWork]

It look as if you are joining the tables on far too many fields, can you not join the tables as Galaxiom suggests using indexes and add all the necessay criteria in the criteria line for all data fields, I don't see how you can speed this because there is so much criteria.

Your table names:

informix_injitd_rec
informix_injitv_rec
informix_ipjtrh_rec
informix_ipjsoh_rec
informix_sahstn_rec
informix_inrcsp_rec

could you not simplify these names to make it all a bit more readable. It may a question of going back to your table structure & design to see if it could be optimised in any way

David

 

[Minty]

In addition to the suggestions above once you have it better organised, don't run your query for last activity date against the existing massive query.

Create smaller sub-queries that only lists customers / products with any sales in the last 90 days. Use these to then query your individual products lines. Finally tie them up as outer joins if you really have to put them in the same report.

 

[namliam]

Holy unreadable sql....
try formatting it before you have people look at it

Select field1
     , field2
from Yourtable
join   thattable on thisfield=thatfield
                and SomeField = anotherField
where  ...
  and ...

You are ill adviced using Date and Value as column names in your query

The reason it may seem slow is that if you execute a query it by default fetches the first "screen full" of data and then behind the scenes fetches the rest.
It may have trouble fetching the first screen if you put in the date criteria...

 

[ConnorGiles]

Also another big thing in databases (well mine anyways)

is after adding a shed load of data to a table or appending via query or such.

A lot becomes corrupt - check your file size of your database. if it looks large, compact and repair it

See if that helps

 

[namliam]

A lot becomes corrupt

Nothing becomes corrupt... please dont make things MUCH worse than they are in reality

 

[ConnorGiles]

namliam, I was relating this to my database and yes it does

compact and repair resolves this - corrupt records boosted the file size of my database and reduced the speed of my database considerably.

 

[namliam]

Yes Connor you ARE special

Your database and records get corrupted, it completely stops working and information get mutated in such a way that it is completely unreadable.

To the rest of us, it is "simply" called bloating, a phenomenon that the space is not freed up after records or any objects being deleted.... But only when doing a compact & repair.

One day one of your databases will actually be corrupted, you will find C&R will not restore it

 

[ConnorGiles]

I was obviously mis informed of the situation then

Bear in mind - I am only a beginner trying to share experience.

compact and repair may work in this case.

 

[jdraw]

jo,

The informix in the name suggests you are using an Informix back end. I don't and haven't used Informix. Your query seems quite complex, and as has been suggested by others, is bringing a lot of data into Access ( and contributing to the slowness). You could try passthrough query, but organizing your request to ask for more specific data and making use of indexes should help.

 

[namliam]

compact and repair may work in this case.

If I am not mistaken Informix is a third party system, http://www-01.ibm.com/software/data/informix/

Compacting your database in that case wont do anything

 

[jobrien4]

Thank you for the suggestion. I will try to better organize it next time I request help.

This query searches through a few million transactions and selects the transactions I'm looking for then cross references that to other tables for more info. I wish I could simplify the query but unfortunately the data I'm look for is so spread out across the database. This is just a subquery for a larger query for a report that is ran though. Overall that query takes about an hour to run. I'm just looking for ways to cut that down.

I'm not able to compact & repair, add indexes, or change table design. As some people noticed, it's using Informix which honestly I don't know much about.

I use an ODBC connection to connect to my ERP's server and get into these tables. In my research for this, some people with similar problems have suggested a Pass Through Query but I don't know how to do one or what benefit that will have. Any input there would be appreciated.

 

[namliam]

My guess is that you are using linked tables, these linked tables you then create a query on.
This has the possible problem that access pulls a lot of data across the network and only does (major) filtering and joining localy.
This can obviously better be done on the database server, which is much more suited to process such amounts of data.

Google can explain how to create pass through queries much better than I can in 2 minutes or less.
https://www.google.nl/search?hl=en-.....1ac.1.34.heirloom-hp..0.43.2331.wRDwsDE8mM0

Biggest difference is that you probably wouldnt be using tables name informix_injitd_rec
but rather injitd_rec (just gambling here) in the query.
Also you wont be able to use access parameters/functions like Date, since the query isnt executed by access anymore. Instead you have to use parameters/functions native to the database you are sending the sql to, i.e. Sysdate for Oracle or GetDate for SQL Server, etc....
Also you may need to (sometimes) re-write the sql to more native versions. Some database (version) still dont work perferctly with ANSI joins and/or may have problems with some of the complex join bracketing that can be done in access, obviously it will be possible in the native version just differently

One final thing I just noticed in your where clause...
((informix_inrcsp_rec.csp_gnrt_std_prd) Like "0")

Like without wildcards is a bad thing! either use = or use wildcards

 

[jdraw]

The advantage of a pass through query is that all of the processing is done on that server. The majority of the data does NOT have to be brought across the network to Access for processing. The pass through query uses the SQL dialog and syntax of the Informix DBMS.

 

[DavidAtWork]

Rather than running this query across linked tables, have you tried importing the source tables and then running this query. Might be worth investigating to see if the time taken to import + time to run the query is > or < the current method

David

 

[namliam]

that is rather reminicant of shooting a nucliar missile at a musquito