SQL Server Compact query runs for around 45 mins

Question

SELECT 
    tbl_sale.SALES_ID,
    tbl_sale.Sales_Date,
    tbl_sale.Sales_Time,
    tbl_users.user_name,
    tbl_sale.customer_id,
    tbl_customer.customer_name,
    tbl_sale.grand_disc,
    tbl_sale.collection_full,
    tbl_sale.term_of_payment,
    tbl_sale.consin1,
    tbl_sale.consin2,
    tbl_sale.narration,
    tbl_sale_details.item_id,
    tbl_item.item_name,
    tbl_sale_details.quantity,
    tbl_sale_details.cost,
    tbl_sale_details.price,
    tbl_sale_details.vat,
    tbl_sale_details.disc,
    tbl_sale_details.total_cost,
    tbl_sale_details.total_price,
    tbl_sale_details.sub_total
FROM
    tbl_customer
INNER JOIN 
    tbl_sale ON tbl_customer.customer_id = tbl_sale.customer_id
INNER JOIN 
    tbl_users ON tbl_sale.User_ID = tbl_users.User_ID
LEFT OUTER JOIN 
    tbl_item 
INNER JOIN 
    tbl_sale_details ON tbl_item.item_id = tbl_sale_details.item_id
    ON tbl_sale.SALES_ID = tbl_sale_details.SALES_ID
WHERE 
    (tbl_sale.Sales_Date >= '1/1/2018'
     AND tbl_sale.Sales_Date <= ' 08/22/2018');

I am a learner. I have a C# code generating a crystal report of all items sold between two dates.

When the dates ranges are small it takes around 3 mins when it is like 8 months. It takes around 45+mins.

The CPU usage is only 7% on my 8 core CPU.

I used CompactView to test this query. The application goes to not responding when this query is in execution

I read that SQL Server Compact Edition inner joins can be slow and tried left joins

I tried creating index on almost on columns in the db to speedup but it is only marginally improved

eg :

CREATE INDEX idxCustId ON tbl_customer(customer_id);

Now I have run out of tricks..

Can anyone advice on what I may be doing wrong?

To expand on what @DragandDrop said; applying indexes on your columns -can- solve a problem, but if you're just going to try-and-fail apply indexes, without any insight into what your're actually doing, you will probably end up doing more harm than good. You need to not only look at the design of the table, but how the use of that table is implemented as well. Indexes should be use like a scalpel, not a shotgun. — WynDiesel, Aug 23 '18 at 06:36
To extend on existing answer, I'm sorry to tell none of them are close to an answer. Query plan cache will occured, and both will have the same performance soon. There is in fact no difference between question query and answers query (except the between but open and close/range on date interval are an other subject). The "only difference" is the cached plan. [Using lower case](https://www.sqlshack.com/understanding-sql-server-query-plan-cache/) for `SELECT AND WHERE` could have the same effect. There is a deep misunderstanding on how sql works.. — Drag and Drop, Aug 23 '18 at 07:12
Hi @rajeswan-perumal, you should try an explain plan of your query, it should give you more insight on how to optimize your query. Welcome to SO, your question is well formulated. — Andy K, Aug 23 '18 at 08:09

score 3 · Accepted Answer · answered Aug 22 '18 at 13:33

3

Its difficult to test this code on my side, without the actual data ... but try this :

SELECT tbl_sale.SALES_ID,
   tbl_sale.Sales_Date,
   tbl_sale.Sales_Time,
   tbl_users.user_name,
   tbl_sale.customer_id,
   tbl_customer.customer_name,
   tbl_sale.grand_disc,
   tbl_sale.collection_full,
   tbl_sale.term_of_payment,
   tbl_sale.consin1,
   tbl_sale.consin2,
   tbl_sale.narration,
   tbl_sale_details.item_id,
   tbl_item.item_name,
   tbl_sale_details.quantity,
   tbl_sale_details.cost,
   tbl_sale_details.price,
   tbl_sale_details.vat,
   tbl_sale_details.disc,
   tbl_sale_details.total_cost,
   tbl_sale_details.total_price,
   tbl_sale_details.sub_total
FROM tbl_customer
INNER JOIN tbl_sale        ON tbl_customer.customer_id = tbl_sale.customer_id 
                            and tbl_sale.Sales_Date between  '1/1/2018' and ' 08/22/2018'
INNER JOIN tbl_users        ON tbl_sale.User_ID = tbl_users.User_ID
INNER JOIN tbl_sale_details ON tbl_sale.SALES_ID = tbl_sale_details.SALES_ID
LEFT OUTER JOIN tbl_item ON tbl_item.item_id = tbl_sale_details.item_id

I basically put the inner joins first, and made the WHERE clause part of the join. This is just a few tricks you can use in order to speed it up the logic.

answered Aug 22 '18 at 13:33

WynDiesel

1,104
7
38

That did the trick but I don't know why this one is fast and mine is slow. Any explanations if you would have the time? – rajeswari perumal Aug 22 '18 at 13:39
See the comment I made in the edit underneath. In short, you're just filtering out data earlier, to make the work "less" later on. – WynDiesel Aug 22 '18 at 13:40
1

Could be because of the order in witch statement are process https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-2017. – Drag and Drop Aug 22 '18 at 13:41
But they exectution plan should have been the same! i recomment reading https://stackoverflow.com/questions/2509987/which-sql-query-is-faster-filter-on-join-criteria-or-where-clause – Drag and Drop Aug 22 '18 at 13:43
My guess is this new querry has a new execution plan that is not cache etc. So it fix the issue without fixing it. – Drag and Drop Aug 22 '18 at 13:47

SQL Server Compact query runs for around 45 mins

1 Answers1