Solved Query Execution Time

[Galaxiom]

The difference between varchars and integers is far less noticable now a days, but the difference is definately there... If only because varchars tend to be longer and less sequential vs integers. 20 years ago it was really really noticable....

Would be interesting to see performance comparisons on modern systems.

Indexing is a science on its own. Different limitations affect the indexing performance as they are created from the retrieval performance. I would expect the indexing performance on larger values to be lower due to the amount of data being processed, but, if the index itself is a hash, it should not make much difference to using the index. No doubt it would depend on the database technology, particularly the specifics of the indexing systems.

Of course all this is a black box to us users except when the database system wizards decide to explicitly reveal something. Unless thoroughly tested, much of what we know about databases comes down to educated guesses because they tell us so little about what really happens in the engine. I guess we should not expect them to reveal all their magical secrets. To be honest, I doubt that I could understand much of it even if they did explain it to me. Set theory isn't trivial.

 

[The_Doc_Man]

Sometimes you get a hint from ORACLE. If you pay to take one of their courses on database management, they will tell you what some of the key structures look like. The SYBASE original engineers came from a company called SHAREBASE and they would tell you about the B-trees and some of the structural elements. They frequently liked to futz around with reworking the "extent" pointers in files so as to be able to insert blocks "in the middle" of a file. Which you CAN do with an indexed file, though not with a sequential one.

However, these days, I guess they guard their proprietary secrets a little better than they used to. Guess they didn't want too many folks trying to reverse engineer their data files.

 

[namliam]

In SQL Server, queries that are similar enough will run reusing the same stored execution plan. The optimiser can easily recognise the similarities between adhoc queries well beyond just changing values embedded in their SQL strings.

erm in theory yes, in practice...
Building an execution plan is usually not the problem...

 

[namliam]

To be honest, I doubt that I could understand much of it even if they did explain it to me. Set theory isn't trivial.

One of the key things for a database to use an index is for it to consider it current.
PK indexes on integer values are always current because new values only get added. Thus rebuilding the index will not help much.

Varchar indexes tend to go out of date much faster.... they tend to be less sequential in nature.
Like the key value here:
[Unique Ref] = '" & Me.[Order_Number].Value & "-" & Me.[Tray_ID].Value & "-" & i & "'
at the very least you will get
1
2
3
and then 10 will be inserted inbetween the 1 and the 2, 11,12,21,22 etc... untill 100 gets inserted again.
Inserting into indexes causes them to be considered out of date, while adding.... i.e.
001
002
010
100
as varchars will make the index perform much better over time without the need to rebuild.

as far as I know and recently been told by an MS Engineer indexes in SQL server do not hash, finding shorter values is easier on a database than finding longer values. Another reason why integers are better even than doubles, but varchars not so good neither.
Offcourse now a days, database servers tend to be "monster machines" with multiple solid state drives that the difference between the one indexed column (type) and the other is hardly measurable in a realiable way.

Worse is fighting the optimizer sometimes... we have some reports and some queries that for reasons unknown run fine 99 / 100 times but the 100th time it desides on a different path to resolve the query.... changing query time from 15 seconds to 15 hours
It has had MS Engineers going crazy for over 6 months now (since december 2019) some even saying "its impossible" untill they see the proof.
The issue seems to have been reduced now to 999/1000.... but it is not funny trying to fix it.

 

[Minty]

The other clever thing with SQL server is backups and restores.

I think unicorns and black holes are involved, as to how else can you explain how SQL can back up 3-4 Gb of data in about 3-4 seconds. And restore it in about 6-7 seconds. (Numbers made up from memory, but you get the drift.)

 

[Bellage]

Well thanks to you all for your support. It appears my execution efficiency was primarily down to the lack of indexes.

It is now executing in a matter of seconds!

I have to say that I'm a little annoyed with myself as creating indexes is normally the first port of call for poor query performance and in my naivety I miss-read the indexable|Yes property to be Indexed|Yes! I've recently had the back-end moved from Access to a SQL server and so I'm fresh when it comes to SSMS.

On the plus side - through your help and advice I have learned some really useful things.

Just for info as it was mentined a couple of times, uDataSet() is a 2D array that is populated as a user processes a tray.

Thanks again.

 

[Isaac]

Well thanks to you all for your support. It appears my execution efficiency was primarily down to the lack of indexes.

It is now executing in a matter of seconds!

I have to say that I'm a little annoyed with myself as creating indexes is normally the first port of call for poor query performance and in my naivety I miss-read the indexable|Yes property to be Indexed|Yes! I've recently had the back-end moved from Access to a SQL server and so I'm fresh when it comes to SSMS.

On the plus side - through your help and advice I have learned some really useful things.

Just for info as it was mentined a couple of times, uDataSet() is a 2D array that is populated as a user processes a tray.

Thanks again.

Great to hear you got some improvement!

 

[Pat Hartman]

In SQL Server, queries that are similar enough will run reusing the same stored execution plan. The optimiser can easily recognise the similarities between adhoc queries well beyond just changing values embedded in their SQL strings.

I'm sure it does but I doubt that it is remembering queries sent to it by other clients. If you have a stored procedure that executes the same embedded query over and over again, I agree that it would probably be cached. But these queries are coming one at a time over a remote connection. It seems like it would be more time consuming to cache these one-off queries and search through them each time a new one comes in than it would be to just create a new execution plan. Also, these queries are not identical since they contain embedded data so the task of comparing one to another has to ignore the data embedded within. If your code modifies the embedded SQL String at all, the query is probably not cached. So
Select aa from bb where xx = '123'
is not the same query as
Select aa from bb where xx = '234'

You would have to be using a token for the key value

Select aa from bb where xx = @abc

 

[Minty]

@Pat Hartman is correct, parameterising your queries /stored procedures is definitely the way to get the best out of your execution plans.
Great description of the differences between passing literal values and parameter-based execution plans here https://www.sqlshack.com/query-optimization-techniques-in-sql-server-parameter-sniffing/

 

[Isaac]

@Pat Hartman is correct, parameterising your queries /stored procedures is definitely the way to get the best out of your execution plans.
Great description of the differences between passing literal values and parameter-based execution plans here https://www.sqlshack.com/query-optimization-techniques-in-sql-server-parameter-sniffing/

Well ... that's a little different. Parameter sniffing is actually a 3rd problem down the road. Simply parameterizing your SP's and walking away will result in, as Pat said, a more likely cached and re-used execution plan...and actually cause the param sn. problem.
A cached execution plan to keep being re-used is not necessarily what should be desired.

Parameter sniffing is actually a negative side effect of doing precisely that. And is a problem whereby it keeps using a cached execution plan, but ideally it shouldn't.

I guess what I am trying to say is up until now the emphasis in posts has been as if we wish an execution plan would be always cached and reused, and Minty's excellent article points out that is not necessarily the case. I'll take the cost of a new one generated over an incorrect cached one used most days of the week, but ideally of course, would be to try to design a balance between the two. The cost of generating a new plan vs. the cost of using a bad cached plan is one to a hundred potentially.

 

[Galaxiom]

While we are on the subject of caching query plans it is worth mentioning the server configuration option relating to it.
Optimise for ad hoc workloads

It prevents query plans being cached on their first run but stores a stub of each query so it can recognise it being used again when it will store the plan. So if your server is doing a lot of one off queries it will stop the plan cache memory filling up with stuff that isn't needed.

The option is off by default so all plans are cached. You should only consider changing the setting after thoroughly understanding the costs and benefits in the context of your server.

Pinal Dave has a short blog post about it with a good cautionary tale.

 

[Isaac]

While we are on the subject of caching query plans it is worth mentioning the server configuration option relating to it.
Optimise for ad hoc workloads

It prevents query plans being cached on their first run but stores a stub of each query so it can recognise it being used again when it will store the plan. So if your server is doing a lot of one off queries it will stop the plan cache memory filling up with stuff that isn't needed.

The option is off by default so all plans are cached. You should only consider changing the setting after thoroughly understanding the costs and benefits in the context of your server.

Pinal Dave has a short blog post about it with a good cautionary tale.

Nice, thank you for posting.