|
There are umpteen reasons why a database may start having performance issues - it is unlikely to be your unchanged code!
This post lists some of the troubleshooting steps you can try to either find out what is wrong and/or just do some housekeeping that should help [SOLVED] SQL Server database slowness troubleshooting - Spiceworks[^]
|
|
|
|
|
Found it...
Between 2 recreations and data repopulation.. someone one changed the primary key of many tables that were used in joins from CLUSTERED to NONClUSTERED index
That seemed like a good idea at the times, they said....
|
|
|
|
|
Check the execution plan. Check that you have the correct indexes on the tables. Try running Brent Ozar's First Responder Kit[^] on the server to see if there are any obvious errors.
You mention that the size of the data has increased. Does it now exceed the server's available memory? If it keeps having to go back to disk to load the data, then that can dramatically slow things down. Especially if the data isn't on an SSD.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Found it...
Between 2 recreations and data repopulation.. someone one changed the primary key of many tables that were used in joins from CLUSTERED to NONClUSTERED index
That seemed like a good idea at the times, they said....
|
|
|
|
|
|
Check your index if the fragmentation is high then re organize it or re index. And If you have index like non cluster or cluster and you changed it or add new index please review.thanks
|
|
|
|
|
|
Not legally, no.
Which means you will get no help here.
Sent from my Amstrad PC 1640
Never throw anything away, Griff
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
|
|
Hello All,
I am looking for some help with the following query. The results I get back are fine as long as there is data in the ShiftData table that meets the criteria. If there is no data in the ShiftDaa table then my query returns nothing. What I am after is to return back all LoopCodes and Choppers from MapBushingData table and zeros if there are no items in ShiftData table matching the criterion. Any help is most appreciated.
SELECT MapBushingData.LoopCode,
MapBushingData.Chopper,
Sum(ShiftData.BRKS) AS Total_BRKS,
Sum(ShiftData.BBOH) AS Total_BBOH,
Avg(ShiftData.DTAB) AS Avg_DTAB
FROM ShiftData
INNER JOIN MapBushingData ON ShiftData.[Position] = MapBushingData.LoopCode WHERE ShiftData.ShiftDate >= cast(? as date) AND ShiftData.ShiftDate <= cast(? as date)
GROUP BY MapBushingData.Chopper,MapBushingData.LoopCode
Thanks,
Frank
|
|
|
|
|
A couple of tweaks is all you are going to need:
Using an INNER JOIN requires matching record to be in both tables.
To get all records from one table regardless if it matches or not you would use either a LEFTRIGHT JOIN. As you want all the records from the second table in the ON connector it would be a RIGHT JOIN
The second thing is dealing with returning 0 if no records match. Relatively easy, just slap an ISNULL (or COALESCE for ANSI SQL) with 0 as the null replacement.
My first attempt at this came up with
SELECT m.LoopCode
, m.Chopper
, Total_BRKS = IsNull(Sum(s.BRKS), 0)
, Total_BBOH = IsNull(Sum(s.BBOH), 0)
, Avg_DTAB = IsNull(Avg(s.DTAB), 0)
FROM @ShiftData s
RIGHT JOIN @MapBushingData m ON s.[Position] = m.LoopCode
WHERE ( s.ShiftDate >= cast('02/03/2011' as date)
AND s.ShiftDate <= cast('03/04/2013' as date)
)
OR s.ShiftDate IS NULL
GROUP BY m.Chopper,m.LoopCode And in testing I found out that IF there is a matched record BUT the date was out of range it would not return the record.
If this is desired; great.
If not, we are going to tweak how the data is JOINed together by relocating the WHERE clauses to also be part of the JOIN connections. And this is what I came up with
DECLARE @ShiftData TABLE (ndx int identity(1,1) not null, Position int, ShiftDate Date, BRKS int, BBOH int, DTAB int)
INSERT @ShiftData
VALUES (1, '10/11/2012', 1, 2, 3)
, (2, '12/11/2013', 3, 4, 5)
, (3, '10/11/2011', 1, 2, 3)
DECLARE @MapBushingData TABLE (ndx int identity(1,1) not null, LoopCode int, Chopper varchar(10))
INSERT @MapBushingData
VALUES (1, 'Chopper 1')
, (2, 'Chopper 2')
, (3, 'Chopper 3')
Director of Transmogrification Services
Shinobi of Query Language
Master of Yoda Conditional
|
|
|
|
|
Just noticed a block of code is missing, will get this updated within the next couple of hours
Director of Transmogrification Services
Shinobi of Query Language
Master of Yoda Conditional
|
|
|
|
|
Reworked my query as shown below and all is good now. Thank you very much for your response.
SELECT m.LoopCode
, m.Chopper
,
SUM(CASE WHEN s.ShiftDate >= cast(? as date) and s.ShiftDate <= cast(? as date)
THEN s.BRKS ELSE 0
END) AS Total_BRKS
,
SUM(CASE WHEN s.ShiftDate >= cast(? as date) and s.ShiftDate <= cast(? as date)
THEN s.BBOH ELSE 0
END) AS Total_BBOH
,
AVG(CASE WHEN s.ShiftDate >= cast(? as date) and s.ShiftDate <= cast(? as date)
THEN s.DTAB ELSE 0
END) AS Avg_DTAB
FROM ShiftData s
RIGHT JOIN MapBushingData m ON s.[Position] = m.LoopCode
GROUP BY m.Chopper,m.LoopCode
ORDER BY m.LoopCode asc
|
|
|
|
|
Glad I could be of assistance; and I guess I don't need to push in the other code sample
Director of Transmogrification Services
Shinobi of Query Language
Master of Yoda Conditional
|
|
|
|
|
Hello,
First of all sorry for putting like a code dump here... I'll try to explain it as clearly as possible, but given my inexperience with SQL (years and years without touching it) I think it is better if I post the two queries completely rather than asking a generic question.
I have two SQL queries that work well.
I need to mix them and I don't know how to do it.
The first one returns me all the details of the Invoice and the customer name, and the second one gives me the invoice cost after making the right selections and calculations (more on this after).
tInvoices fields are: id, invoiceNumber, invoiceSeries, date, customerId, notSent
tCustomers fields are: id, name, ...
And the first query is simple:
SELECT tInvoices.*, tCustomers.name FROM tInvoices INNER JOIN tCustomers ON CustomerId = tCustomers.id; That way I get all the invoice details and the customer name in each row to show it in an HTML table.
But I also want to put the invoice price in that table and this is much more complicated:
I have a structure in which there is a reference price per customer, project and user... this means that depending on what's agreed with each customer for each project, each user will have a cost...
If I go to a customer company to program something I could get X€ and other guy from the company could get Y€. And those proces could be V€ and W€ for another project for the same customer...
Appart of that I've set in each task a special cost field. If that cost is filled (not NULL) then I'll use that cost as an exception (again something pacted with the customer).
All this said... this happens with the tasks (work done in a project by a human being) and with the expenses (hotel, gasoline, meals... tickets in general).
These are the tables involved:
tTasks fields are: id, projectId, userId, taskTypeId, taskUseId, invoiceId, date, startTime, endTime, price, notes
tReferencePricesForTasks fields are: projectId, userId, taskTypeId, price
tTasksTypes fields are: id, name
tExpenses fields are: id, projectId, userId, expenseTypeId, invoiceId, date, quantity, price, notes
tReferencePricesForExpenses fields are: projectId, userId, espenseTypeId, price
tExpensesTypes fields are: id, name, allowEditingAmounts
And the second query is not that simple, but seems to work well:
SELECT SUM(subTotal) AS total FROM
(
SELECT
(
CASE WHEN tTasks.taskUseId = 1 THEN
(
CASE WHEN price IS NULL THEN
(
SELECT
tReferencePricesForTasks.price
FROM tReferencePricesForTasks
WHERE tReferencePricesForTasks.taskTypeId = tTasks.taskTypeId AND
tReferencePricesForTasks.projectId = tTasks.projectId AND
tReferencePricesForTasks.userId = tTasks.userId
)
ELSE
price
END
)
ELSE
0
END
)
*
(
SELECT
ROUND(TIME_TO_SEC(TIMEDIFF(endTime, startTime)) / 3600,2)
)
AS subTotal
FROM
tTasks
LEFT JOIN tTasksTypes ON tTasks.taskTypeId = tTasksTypes.id
WHERE tTasks.taskUseId <> 3 AND
tTasks.invoiceId = '.$PHP_Invoice_ID_VALUE.'
UNION ALL
SELECT
(
CASE WHEN price IS NULL THEN
(
SELECT
ROUND(tReferencePricesForExpenses.preu,2)
FROM tReferencePricesForExpenses
WHERE tReferencePricesForExpenses.expenseTypeId = tExpenses.expenseTypeId AND
tReferencePricesForExpenses.projectId = tExpenses.projectId AND
tReferencePricesForExpenses.userId = tExpenses.userId
)
ELSE
ROUND(cost, 2)
END
)
*
round(tExpenses.quantity,2)
AS subTotal
FROM
tExpenses
LEFT JOIN tExpensesTypes ON tExpenses.expenseTypeId = tExpensesTypes.id
WHERE tExpenses.invoiceId = '.$PHP_Invoice_ID_VALUE.'
)
AS VIEW1 As you can see in my second query, I'm using $PHP_Invoice_ID_VALUE which is a PHP variable that I update in each row of the first query to get the Invoice ID.
I'd like to mix both queries, but I don't know how to do it.
The idea is not needing to call the second query in each row... that way everything should be faster and I would be able to apply filters and orders given the price too...
I would like to substitute the PHP variables for each invoice listed in the first query.
Using Mysql and MariaDB.
Sorry for the code dump, but I truly don't know how to ask it generically without missing any detail.
Thank you very much for your help.
|
|
|
|
|
Try something like this:
WITH ctePrices As
(
SELECT
tTasks.invoiceId,
(
CASE WHEN tTasks.taskUseId = 1 THEN
(
CASE WHEN price IS NULL THEN
(
SELECT tReferencePricesForTasks.price
FROM tReferencePricesForTasks
WHERE tReferencePricesForTasks.taskTypeId = tTasks.taskTypeId
AND tReferencePricesForTasks.projectId = tTasks.projectId
AND tReferencePricesForTasks.userId = tTasks.userId
)
ELSE
price
END
)
ELSE
0
END
)
*
(
ROUND(TIME_TO_SEC(TIMEDIFF(endTime, startTime)) / 3600, 2)
)
AS subTotal
FROM
tTasks
LEFT JOIN tTasksTypes ON tTasks.taskTypeId = tTasksTypes.id
WHERE
tTasks.taskUseId <> 3
UNION ALL
SELECT
tExpenses.invoiceId,
(
CASE WHEN price IS NULL THEN
(
SELECT ROUND(tReferencePricesForExpenses.preu,2)
FROM tReferencePricesForExpenses
WHERE tReferencePricesForExpenses.expenseTypeId = tExpenses.expenseTypeId
AND tReferencePricesForExpenses.projectId = tExpenses.projectId
AND tReferencePricesForExpenses.userId = tExpenses.userId
)
ELSE
ROUND(cost, 2)
END
)
*
round(tExpenses.quantity, 2)
AS subTotal
FROM
tExpenses
LEFT JOIN tExpensesTypes ON tExpenses.expenseTypeId = tExpensesTypes.id
),
cteTotals As
(
SELECT
invoiceId,
SUM(subTotal) As total
FROM
ctePrices
GROUP BY
invoiceId
)
SELECT
tInvoices.*,
tCustomers.name,
cteTotals.total
FROM
tInvoices
INNER JOIN tCustomers ON tInvoices.CustomerId = tCustomers.id
INNER JOIN cteTotals ON cteTotals.invoiceId = tInvoices.id
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
The first CTE can probable be simplified to:
SELECT tTasks.invoiceId
,CASE
WHEN tTasks.taskUseId = 1
THEN COALESCE(tTasks.price,tReferencePricesForTasks.price)
ELSE 0
END * (ROUND(TIME_TO_SEC(TIMEDIFF(endTime, startTime)) / 3600, 2)) AS subTotal
FROM tTasks
JOIN tReferencePricesForTasks
ON tReferencePricesForTasks.taskTypeId = tTasks.taskTypeId
AND tReferencePricesForTasks.projectId = tTasks.projectId
AND tReferencePricesForTasks.userId = tTasks.userId
WHERE tTasks.taskUseId <> 3
UNION ALL
SELECT tExpenses.invoiceId
,ROUND(COALESCE(tExpenses.price,tReferencePricesForExpenses.preu), 2) * round(tExpenses.quantity, 2) AS subTotal
FROM tExpenses
JOIN tReferencePricesForExpenses
ON tReferencePricesForExpenses.expenseTypeId = tExpenses.expenseTypeId
AND tReferencePricesForExpenses.projectId = tExpenses.projectId
AND tReferencePricesForExpenses.userId = tExpenses.userId
I would look over the roundings though
|
|
|
|
|
First of all, thank you very much for your answer!
I've seen the CTE concept in the Internet... it would make this a little bit easier... the problem is that PHPMyAdmin doesn't like the clause "WITH" and therefore I can't test it...
I'm thinking of storing the total price value into a new column of the invoice table and recalculate it after creating it or each time I modify it... It probably will be much faster when getting all the data and I can't see why it would be a bad design... but I'm super novice... Would it be something terrible?
And I'm curious... why would you look at the roundings?
Thanks again!
|
|
|
|
|
So inline the CTEs then:
SELECT tInvoices.*
,tCustomers.name
,cteTotals.total
FROM tInvoices
INNER JOIN tCustomers ON tInvoices.CustomerId = tCustomers.id
INNER JOIN (
SELECT invoiceId
,SUM(subTotal) AS total
FROM (
SELECT tTasks.invoiceId
,CASE
WHEN tTasks.taskUseId = 1
THEN COALESCE(tTasks.price, tReferencePricesForTasks.price)
ELSE 0
END * (ROUND(TIME_TO_SEC(TIMEDIFF(endTime, startTime)) / 3600, 2)) AS subTotal
FROM tTasks
JOIN tReferencePricesForTasks ON tReferencePricesForTasks.taskTypeId = tTasks.taskTypeId
AND tReferencePricesForTasks.projectId = tTasks.projectId
AND tReferencePricesForTasks.userId = tTasks.userId
WHERE tTasks.taskUseId <> 3
UNION ALL
SELECT tExpenses.invoiceId
,ROUND(COALESCE(tExpenses.price, tReferencePricesForExpenses.preu), 2) * round(tExpenses.quantity, 2) AS subTotal
FROM tExpenses
JOIN tReferencePricesForExpenses ON tReferencePricesForExpenses.expenseTypeId = tExpenses.expenseTypeId
AND tReferencePricesForExpenses.projectId = tExpenses.projectId
AND tReferencePricesForExpenses.userId = tExpenses.userId
) ctePrices
GROUP BY invoiceId
) cteTotals ON cteTotals.invoiceId = tInvoices.id
The problem I have with rounding is that you're rounding to early, and even using a product of two roundings in the sum.
That way you're creating a rounding error: Round-off error - Wikipedia[^]
|
|
|
|
|
It looks incredibly easy once you see it!
I'll try it!
Thank you very much!
|
|
|
|
|
It is.
The purpose of a CTE is to make the code more readable.
when the query is analyzed by the optimizer all CTEs are inlined.
|
|
|
|
|
First of all thank you very much for your answer!
PHPMyAdmin doesn't know the word "WITH"...
Can't even try it...
I'm trying to avoid the need to do two queries while populating a table...
Would it be a bad design to add that totalInvoicePrice into the table tInvoices? that way at the moment of creating the invoice I would do what it's working now... and when I would need to get the data it would be extremely easy...
|
|
|
|
|
Try inserting a semicolon just before WITH keyword:
;WITH ctePrices AS...
while (!(success = Try()));
|
|
|
|
|
Exactly the same...
|
|
|
|
|