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?

Answer 1

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.