My sql query times out if the database slightly becomes large
- or -
Post a project like this$70
- Posted:
- Proposals: 9
- Remote
- #2112737
- Expired
187613021018611875661205833323493532349965220202623689352350164
Description
Experience Level: Entry
Hi,
I have a SQL query which works fine if the SQL server database is too small but server times out as the database enlarges.
SQL query is:
SELECT 1 AS branch, YEAR(th.Date1) * 10000 + MONTH(th.Date1) * 100 + DAY(th.Date1) AS DayOf, i.Barcode AS upc, ISNULL(i.ProductName,'Cash Out') AS description, '' AS Supplier, MAX(th.Date1) AS DateTo, SUM(tl.Quantity) AS TotalQty,
SUM(tl.Price * tl.Quantity) AS TotalValue, MAX(th.TID) AS lastid
FROM Transactions AS th LEFT OUTER JOIN
ProductSell AS tl ON tl.TransactionID = th.TransactionID LEFT OUTER JOIN
Product AS i ON i.ProductID = tl.ProductID
WHERE (th.TID > 0) AND (th.Date1 >= @x) AND (th.Date1 <= @y)
Group BY th.Date1, i.Barcode, i.ProductName
ORDER BY lastid
I have a SQL query which works fine if the SQL server database is too small but server times out as the database enlarges.
SQL query is:
SELECT 1 AS branch, YEAR(th.Date1) * 10000 + MONTH(th.Date1) * 100 + DAY(th.Date1) AS DayOf, i.Barcode AS upc, ISNULL(i.ProductName,'Cash Out') AS description, '' AS Supplier, MAX(th.Date1) AS DateTo, SUM(tl.Quantity) AS TotalQty,
SUM(tl.Price * tl.Quantity) AS TotalValue, MAX(th.TID) AS lastid
FROM Transactions AS th LEFT OUTER JOIN
ProductSell AS tl ON tl.TransactionID = th.TransactionID LEFT OUTER JOIN
Product AS i ON i.ProductID = tl.ProductID
WHERE (th.TID > 0) AND (th.Date1 >= @x) AND (th.Date1 <= @y)
Group BY th.Date1, i.Barcode, i.ProductName
ORDER BY lastid
Ranjan P.
0% (0)Projects Completed
-
Freelancers worked with
-
Projects awarded
0%
Last project
8 May 2024
New Zealand
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
Hi Ranjan,
Can you tell me the row cound and total size of each table? And any indexes you have on them?
James
696890
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies