|
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
|
|
|
|
|
You mean like Apr for 04?
DECLARE @date datetime2 = '2000-01-01';
SELECT FORMAT(@date, 'MMM') AS 'FORMAT';
Hmm...
I would of never considered that. Will give it a test and see how it goes.
Haven't done anything today with that SQL statement yet.
Thanks!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Using yyyyMMdd , with no separators, is unambiguous to SQL Server.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I ended up with this. I didn't want to do too much change at one time, and focused on preserving the date formats that feed into the SQL statements via PHP. eg. '2021-04-10'. I made sure that the month and day were all like 04 instead of 4.
And my head scratchier was a pretty stupid mistake; I translated the code late in the month, so the day was 21. When the month rolled over the day 08 was 8. I went back through my PHPV7.4 and fixed how I crafted the dates.
This was just one of probably 30 SQL statements I fixed. But now my results are matching the original code written in 2003.
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 ((CONVERT(char(10), b.startup_check_date, 120) >= '$firstDate' AND CONVERT(char(10), b.startup_check_date, 120) <= '$lastDate')
OR (CONVERT(char(10), b.finished_check_date, 120) >= '$firstDate' AND CONVERT(char(10), b.finished_check_date, 120) <= '$lastDate')
OR (b.startup_check_date IS NULL OR CONVERT(char(10), b.startup_check_date, 120) = '1900-01-01')
OR ((CONVERT(char(10), b.startup_check_date, 120) <= '$firstDate')
AND (b.finished_check_date IS NULL OR CONVERT(char(10), b.finished_check_date, 120) = '1900-01-01')))
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Hi All
I am working to move an unwieldy spreadsheet into a streamlined database to improve the data handling, speed of use and data integrity. I kind of understand what it is I want to do (to a degree) but am new to data modelling/database design so need to get assistance on a particular situation that currently has me stumped as to how to best overcome/design around.
I currently have the data split into 4 tables based on the unique and the repeated/copied data in the spreadsheet. The data is around delivery of goods and split as follows:
Consignment which can be made up of 1 or multiple warehouse deliveries
Warehouse deliveries which can be made up of 1 or multiple customer deliveries
So far there are 3 of the 4 tables, there MUST be at least 1 warehouse delivery for a consignment and there MUST be at least 1 customer delivery for a warehouse delivery
The 4th table is Product. Currently a customer delivery can be 1 or multiple products so I believe that the Product should be related to the Customer Delivery table, but the wrinkle is that at the start of the process we will have a Consignment with Product(s) but not necessarily the Customer information until a later date. Currently in the spreadsheet what happens is the data is entered on one line with no Customer/Warehouse information and just a total for the product and then at a later date it is split into multiple lines of data with alot of repeated information but the specific Warehouse/Customer/Product Split information updated. So previously I stated there MUST be warehouse deliveries and customer deliveries for every consignment and whilst this is true the nature of the business means that initially that information may not be available until some time after the consignment is en route to the warehouse
Hope this is all making sense so far.
So here's my question. Should the Product table be related to Consignment or Customer Delivery, and if customer delivery is it a matter of Warehouse and Customer delivery details being entered in a dummy format to allow the relationships to be setup and manipulated at a later date
I really hope this all makes sense as i'm trying to do this in the right way but my low knowledge on database design means i'm kind of making it up as i go by reading up on the right way to do things from multiple sources and pulling together examples of potentially similar standard designs and copy/pasting sections from them to cobble together the data model
thanks in advance
Glen
|
|
|
|
|
Couple of things that might help while you are waiting for a more definitive answer ...
Here is a list of tutorials on Database Design DatabaseAnswers Tutorials[^]
The same site provides pre-designed schemas for multiple scenarios - see http://www.databaseanswers.org/data_models/index_all_models.htm[^]
My gut feelings for your problem:
In my head Products need to be associated to Customer Orders (deliveries). I'm not sure what sort of business model you are using if you know what products are going to be in a consignment without any customer orders to back them up. It sounds as if you are going to need a "release" process for a consignment that will do the validation on missing components / relationships
|
|
|
|
|