|
Please don't repost if your comment does not appear immediately: all of these went to moderation and required a human being to review them for publication. In order to prevent you being kicked off as a spammer, all three had to be accepted, and then I have to clean up the spares. Have a little patience, please!
I've deleted the other versions.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Thank you for the clean up. I didn't repost. It was a bug. When I pressed the Post bottom, then no action happens. after a while I try again. I think because of my poor internet connection that happens.
SignatureNotFoundException
|
|
|
|
|
For example i have two pc in a LAN. pc1 has sql server installed and management studio also but pc has only management studio. when i execute a SP by management studio in pc1 then output comes after 4 minutes but when i execute the same SP by management studio in pc2 then output comes after 11 minutes. both pc in same LAN then why pc2 takes long time to show data.
please suggest some guide line to diagnosis this problem. how to figure out where the problem lies?
how to capture is it sql server issue or network issue ?
please help in such a way as a result i should be able to capture where the problem is. thanks
|
|
|
|
|
|
What are the record counts (result set)? Everything else is just guessing at this point.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Record count may be irrelevant as the SP is probably doing a bunch of processing.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
He's comparing "on server" with "client and server". He says the client setup runs "slower".
What role is the client playing?
Do you know how many records he's downloading and what the impact is?
Does he report progress on the first record (async); or does he download an entire result?
...
(I find one "open" question is often better (for OP) than a barrage of detailed ones).
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
I have a large parameterised SQL query, on SQL Server
When I run it in SQL Server Management Studio (with SQL, doh) or in LinqPad (use C# Linq to Sql) then it runs fast.
If I ran it meself in my console app whether with EFCore/Linq Query or even a SqlCommand (and SQL Text) it is horrendously slow... even though I use the same parameter for every tests... (you know, if it created a stupid index for the query or something)
I have no clue what else I can do to check/fix that... 
modified 28-Apr-21 1:25am.
|
|
|
|
|
Some good suggestions here:
Slow in the Application, Fast in SSMS?[^]
I've often found the connection settings cause this - particularly the ARITHABORT setting, which is ON for SSMS, but OFF for ADO.NET.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks, will investigate tomorrow! 
|
|
|
|
|
Also take a look at parameter sniffing[^]. I know it is weird but it does make a difference sometimes.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I'm tryin to figure out how to get a schedule based on a start time and the hourly interval in TSQL. I've been searching the whole day and I just can't find an idea on how to do it.
SELECT
'01:00' AS StartTime
,4 AS Hours
What I need is a column that has the the times listed for the whole day:
StartTime Hours Schedule
01:00 4 01:00,05:00,09:00,13:00,17:00,21:00
|
|
|
|
|
|
You'll need a tally table and a way to concatenate multiple string values. If you're using SQL Server 2017 or later, you can use STRING_AGG[^]; otherwise, use an alternative method[^].
For example:
SELECT
StartTime,
Hours,
STUFF(T.ScheduleTime, 1, 1, '') As ScheduleTime
FROM
YourSourceTable As S
CROSS APPLY
(
SELECT
',' + CAST(T.ScheduleTime As char(5))
FROM
(
SELECT TOP ((1 + DateDiff(hour, S.StartTime, '23:00')) / S.Hours)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As N
FROM
sys.all_columns
) As N
CROSS APPLY
(
SELECT DateAdd(hour, N.N * S.Hours, S.StartTime) As ScheduleTime
) As T
FOR XML PATH('')
) As T (ScheduleTime)
; Demo[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
This is EXACTLY what I needed. This was far too complex for me to comprehend, so I started to look into why it works and breaking down everything in piece. No comments in anything for the query below, but should be able to figure it out for someone looking at the same thing:
SELECT
DATEDIFF(HOUR, '1:00', '23:00')
SELECT
DATEDIFF(HOUR, '1:00', '23:00') / 4
SELECT
(1 + DATEDIFF(HOUR, '1:00', '23:00')) / 4
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM
sys.all_columns
SELECT TOP ((1 + DateDiff(hour, '1:00', '23:00')) / 4)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As N
FROM
sys.all_columns
SELECT
DATEADD(HOUR, 5 * 4, '1:00') As ScheduleTime
UNION
SELECT
DATEADD(HOUR, 4 * 4, '1:00') As ScheduleTime
UNION
SELECT
DATEADD(HOUR, 3 * 4, '1:00') As ScheduleTime
UNION
SELECT
DATEADD(HOUR, 2 * 4, '1:00') As ScheduleTime
UNION
SELECT
DATEADD(HOUR, 1 * 4, '1:00') As ScheduleTime
SELECT
',' + CAST(T.ScheduleTime As char(5))
FROM
(
SELECT TOP ((1 + DateDiff(hour, '1:00', '23:00')) / 4)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As n
FROM
sys.all_columns
) As N
CROSS APPLY
(
SELECT DateAdd(hour, N.n * 4, '1:00') As ScheduleTime
) As T
FOR XML PATH('')
I had to make an adjustment to the final query though to make sure I was getting a schedule for a 24 hour period instead from the start time to midnight:
WITH cteSource As
(
SELECT CAST('13:00' As time) As StartTime, 4 As Hours
UNION SELECT CAST('01:00' As time) As StartTime, 7 As Hours
)
SELECT
StartTime,
Hours
,STUFF(T.ScheduleTime, 1, 1, '') As ScheduleTime
FROM
cteSource As S
CROSS APPLY
(
SELECT
',' + CAST(T.ScheduleTime As char(5))
FROM
(
SELECT TOP (24 / S.Hours)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As n
FROM
sys.all_columns
) As N
CROSS APPLY
(
SELECT DateAdd(hour, N.n * S.Hours, S.StartTime) As ScheduleTime
) As T
FOR XML PATH('')
) As T (ScheduleTime)
modified 29-Apr-21 15:03pm.
|
|
|
|
|
Please share any Retail product inventory management database schema.
|
|
|
|
|
|
|
Why?
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Pick one data models[^]
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I'm wondering if I went too far in converting all these dates. While I was testing, I think the database server crashed and the connection broke. I know I asked this before about converting date times, and I thought I fixed this in Feb because it was working fine. But I'm back to it again.
SELECT
a.project_no,
a.status,
a.Sales_no,
a.swan_job,
b.tc,
b.EC,
convert(VARCHAR(10), b.startup_check_date, 120),
convert(VARCHAR(10), b.finished_check_date, 120),
e.Employee_ID,
e.fname,
e.lname,
c.customer_no,
c.lname,
c.fname
FROM project AS a, commission_summary AS b, employee AS e, customer AS c
WHERE a.project_no = b.project_no
AND a.customer_no = c.customer_no AND a.sales_no = e.Employee_ID
AND (a.status = 'construction' OR a.status = 'finished')
AND (convert(VARCHAR(10), b.startup_check_date, 120) BETWEEN '2021-4-01' AND '2021-4-31')
OR (convert(VARCHAR(10), b.finished_check_date, 120) BETWEEN '2021-4-01' AND '2021-4-31')
OR (convert(VARCHAR(10), b.startup_check_date, 120) IS NULL
OR convert(VARCHAR(10), b.startup_check_date, 120) = '1900-01-01')
OR (convert(VARCHAR(10), b.startup_check_date, 120) < '2021-4-01')
AND (convert(VARCHAR(10), b.finished_check_date, 120) IS NULL OR convert(VARCHAR(10), b.finished_check_date, 120) = '1900-01-01')
ORDER BY a.status, b.finished_check_date, b.startup_check_date
Original SQL Statement:
I don't understand the FLOOR statement in the cast. Does that make the date at midnight or 0:01
SELECT
a.project_no,
a.status,
a.Sales_no,
a.swan_job,
b.tc,
b.EC,
convert(CHAR(10),
b.startup_check_date,120),
convert(CHAR(10),
b.finished_check_date,120),
e.Employee_ID,
e.fname,
e.lname,
c.customer_no,
c.lname,
c.fname
FROM project as a, commission_summary as b, employee as e , customer as c
WHERE a.project_no=b.project_no
AND a.customer_no=c.customer_no
AND a.sales_no = e.Employee_ID
AND (a.status ='construction' or a.status ='finished')
AND ((CAST(FLOOR(CAST(b.startup_check_date AS FLOAT))AS DATETIME) BETWEEN '$firstDate' AND '$lastDate')
OR (CAST(FLOOR(CAST(b.finished_check_date AS FLOAT))AS DATETIME) BETWEEN '$firstDate' AND '$lastDate')
OR (b.startup_check_date is NULL or b.startup_check_date = '1900-01-01')
OR ((CAST(FLOOR(CAST(b.startup_check_date AS FLOAT))AS DATETIME) < '$firstDate' )
AND (b.finished_check_date is NULL or b.finished_check_date = '1900-01-01')))";
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
jkirkerx wrote:
Original SQL Statement:
I don't understand the FLOOR statement in the cast. Does that make the date at midnight or 0:01
From MSDN FLOOR (Transact-SQL) - SQL Server | Microsoft Docs :
Quote: Returns the largest integer less than or equal to the specified numeric expression.
I'd expect "Quote: the date at midnight
"
|
|
|
|
|
Don't convert dates to strings in order to compare them. If you just want to ignore the time part, cast them to date instead:
CAST(b.startup_check_date As date) Between '20210401' And '20210431' However, this will be non-SARGable. A better option would be to use:
(b.startup_check_date >= '20210401' And b.startup_check_date < '20210501')
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
This makes more sense to me now.
so I need to make sure that I craft the dates, like 04 for April by adding the 0.
And on the Select statement, I can convert to string to get the output in the format that I want,
But on the condition statement, convert to the format that SQL server works the best with.
No wonder why I crashed the database server.
I'll give it a try today.
Thanks Richard.
And Thanks to the other guy about the Floor
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
if you must craft a date then use the short name of the month it removes all ambiguity from the expression and SQL Server likes it.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
