SQL Server query

Johnatan56 said:
Group by customer ID and order it, would do top 3 to reduce size. That then in a temp table and select the row number. https://stackoverflow.com/questions/1022514/selecting-nth-record-in-an-sql-query

Outside of sql server, Maria db has limit with offset, think only oracle and ms don't have support still, but haven't used oracle in 5 years and haven't had a need to solve a problem like this in a long time where didn't need the result sets other results as well.
Click to expand...

Would have to have a "count" in there somewhere, I reckon

www.zentut.com

SQL COUNT - Returns the Number of Rows in a Specified Table

In this tutorial, you will learn about the SQL COUNT function that returns the number of rows in a specified table.
www.zentut.com www.zentut.com
 
Nice necro can't believe I missed this debate years ago.

Basic rule of thumb question for store procedures use is , is the workload database intensive or require multiple trips from your main engine.
 
_kabal_ said:
Linqpad is great. Pity it only works on windows
Click to expand...

Why it's great:
  • You can import existing EF6 and EF Core DB Context's into it
  • You can live build LINQ queries without having to do this in actual code, and run an application (you can probably achieve the same thing with a unit test, but it's not as slick and seamless)
  • You can update your DB context in your IDE (add new models, update existing models), and then just build (not run) the project containing the DB context, and it will be available in LINQPad
  • Easily see the SQL generated, so newcomers can understand what LINQ expressions are actually doing
 
I'm back on this, had a crap ton of things to do this morning. I'll post it when done. Need coffee.
 
_kabal_ said:
Why it's great:
  • You can import existing EF6 and EF Core DB Context's into it
  • You can live build LINQ queries without having to do this in actual code, and run an application (you can probably achieve the same thing with a unit test, but it's not as slick and seamless)
  • You can update your DB context in your IDE (add new models, update existing models), and then just build (not run) the project containing the DB context, and it will be available in LINQPad
  • Easily see the SQL generated, so newcomers can understand what LINQ expressions are actually doing
Click to expand...
^thats only the ‘sql’ features, there’s tons more.
Learning about expression trees or visualising concepts like IAsyncEnumerable.

It’s a really powerful tool.
 
As a C/php developer (someone who breathes it), let me tell you, stored procedures are the devil's dick. Don't suck it.

There's always a better way to do it.
In the very rare edge case where it is needed, there's still a better way, but if you absolutely insist then please be incredibly mindful about it.
 
Thor said:
As a C/php developer (someone who breathes it), let me tell you, stored procedures are the devil's dick. Don't suck it.

There's always a better way to do it.
In the very rare edge case where it is needed, there's still a better way, but if you absolutely insist then please be incredibly mindful about it.
Click to expand...
You have a better way to establish a baseline for metrics to monitor for regressions?
 
Thor said:
As a C/php developer (someone who breathes it), let me tell you, stored procedures are the devil's dick. Don't suck it.

There's always a better way to do it.
In the very rare edge case where it is needed, there's still a better way, but if you absolutely insist then please be incredibly mindful about it.
Click to expand...
Disagree with this, SP can be very useful, it always depends.
Company I work at SP means that the guy doing DB work can focus on the SQL, when I make a PR he can review it without having all the C# code in it that he doesn't really focus much on, he just has to handle the T-SQL. One can see the SQL changes across all the apps, which apps use the attribute.
This has mostly to do with the majority of the SQL side of the system being lots and lots of multiple queries that get aggregated, makes sense to have it in one place and not have the app handle it.

SP is also way easier to modify if e.g. testing new one if your system load is bursty and it's more difficult to push a fix on an app (causing downtime if e.g. a report SP is having an issue, why should everyone else get their connection terminated so you can fix a small semantic error you made forgetting e.g. that edge parameter for those two clients in the report?).

That said, a majority of the time going SP is not a good move, version control is more difficult, most people don't know how to write good SQL and often Entity can write better SQL than some of the stuff I've seen.
For things like Thor's projects, I would not see the point of SP's at all and would find it strange if he used them.

If your load is pretty consistent day to day, measuring regression in normal app is fine/better tbh.
 
Thor said:
Why would you need an SP to monitor regression?
Click to expand...
You don’t need an SP, you could use a View but a query doesn’t give the whole picture for performance.

You need consistency and that’s hard thing to establish in an app environment.
You update your ORM and now EF is generating one query per LINQ statement, or using inner joins, maybe the query is the same but it ‘feels’ slower(different query plan).

SQL Server is smart but sometimes it needs help, that’s why you need to monitor and compare against a baseline.
 
DA-LION-619 said:
You don’t need an SP, you could use a View but a query doesn’t give the whole picture for performance.

You need consistency and that’s hard thing to establish in an app environment.
You update your ORM and now EF is generating one query per LINQ statement, or using inner joins, maybe the query is the same but it ‘feels’ slower(different query plan).

SQL Server is smart but sometimes it needs help, that’s why you need to monitor and compare against a baseline.
Click to expand...
Fair enough, I get what you are saying.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X