UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Best Way To Use Values From A Settings Table As Predicates In A View, Any Versions    
 
   
PDTech
post Jun 19 2017, 11:11 AM
Post#1



Posts: 174
Joined: 8-August 07
From: Doha, Qatar


I have a fairly complex View in which I need to use values from a settings table as predicates.

This is the method I'm using:
CODE
Select ...
From...
Where HoursWorked> (Select Max(Setting) from tblSetting where SettingValue = 'MinHours')


I have to use Max so SQL is sure only one value is being returned, but the SettingValue column is UNIQUE.

I have noticed that I'm getting a large number (over a million) of Logical Reads for tblSetting when I run the query. Rather than just going and getting the value once, clearly SQL is retrieving it repeatedly even though the value will always be the same.

If I hard code the values into the Where clause it seems to shave 20 to 30% off the total execution time.

What are more efficient ways of doing this?

I have experimented with turning my View into an Inline Table Valued Function (I don't think a Multi-Line Table Valued function would be appropriate as I understand SQL won't be able to Optimize it). I can set parameters for the function and pass as parameters as follows:
CODE
Select * from MyFunction(Select Max(Setting) from tblSetting where SettingValue = 'MinHours')
... but I'm wondering if there is a more elegant solution that I'm missing.

--------------------
Paul (visit my page for Access tips, tricks and samples: pdtech.co.UK).
Go to the top of the page
 
theDBguy
post Jun 19 2017, 11:27 AM
Post#2


Access Wiki and Forums Moderator
Posts: 70,635
Joined: 19-June 07
From: SunnySandyEggo


Hi Paul,

Just curious, have you tried simply joining the two tables? For example:

SELECT...
FROM... T1
INNER JOIN tblSetting T2
ON T1.HoursWorked>T2.Setting
WHERE T2.SettingValue='MinHours'

Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
PDTech
post Jun 19 2017, 11:44 AM
Post#3



Posts: 174
Joined: 8-August 07
From: Doha, Qatar


Excellent suggestion.

Unfortunately the server I am using for testing is offline now until tomorrow, but I'll give that a try in the morning and report back if there is an appreciable difference in performance.

--------------------
Paul (visit my page for Access tips, tricks and samples: pdtech.co.UK).
Go to the top of the page
 
theDBguy
post Jun 19 2017, 12:42 PM
Post#4


Access Wiki and Forums Moderator
Posts: 70,635
Joined: 19-June 07
From: SunnySandyEggo


Hi Paul,

I couldn't test it either, so I hope it works when you try it. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
PDTech
post Jun 20 2017, 02:58 AM
Post#5



Posts: 174
Joined: 8-August 07
From: Doha, Qatar


Tried as you suggested but it didn't help with the scan count frown.gif

On further testing I noticed that, even when I use the Table Valued Function (FnTest) and pass the parameters as select statements, I'm still getting the high scan count on tblSetting -e.g.
CODE
Select * from FnTest (Select Max(Setting) from tblSetting where SettingValue = 'MinHour')


Only if I assign the value from tblSetting to a variable first, then pass that variable into the TVF do I eliminate the high scan count on tblSetting and reduce the query processing time - e.g.
CODE
Declare @MinHours
Set @MinHours = Select Setting from tblSetting where SettingValue = 'MinHour'
Select * from FnTest (@MinHours)


When I check the Query Cost in the Actual Execution plan (by running both of the above statements in a single batch) the version that passes the parameter as a variable is 49% of the batch but the version that passes the parameter as a Select statement is 51%.

Despite the similarity in the reported Query Cost, the number of reads on tblSetting is vastly different and the approach that passes the variables is consistently quicker.

--------------------
Paul (visit my page for Access tips, tricks and samples: pdtech.co.UK).
Go to the top of the page
 
theDBguy
post Jun 20 2017, 10:33 AM
Post#6


Access Wiki and Forums Moderator
Posts: 70,635
Joined: 19-June 07
From: SunnySandyEggo


Oh well, sorry to hear it didn't help.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
LPurvis
post Jun 20 2017, 10:59 AM
Post#7


UtterAccess Editor
Posts: 16,057
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

I've nothing much to offer other than, perhaps, surprise.
>> I'm getting a large number (over a million) of Logical Reads for tblSetting

The settings table is the one with so many scans? For one simple aggregation?
Firstly, do you really need the aggregation? (Not that I think this is the cause, I think it's just what the optimiser is choosing. :-s)
What version of SQL Server is refusing to execute without the Max as guarantee that it's a single row returned?
Do you have a screenshot of the expanded details from the icon in question in the execution plan?

Secondly, I can't disagree with your solution (or circumvention of the issue ;-).
I'd perhaps consider just passing the string type by which you want to limit.
e.g.
SELECT * FROM dbo.FnTest('MinHour')
and doing the rest internally.

But umm... yeah. :-s

Cheers

--------------------
Go to the top of the page
 
PDTech
post Jun 21 2017, 05:24 AM
Post#8



Posts: 174
Joined: 8-August 07
From: Doha, Qatar


You are right, having tested the aggregation is not required - perhaps a habit I picked up from an earlier version, Access or slightly different scenario where the aggregation is required so SQL is happy only one value will be returned.

I am testing a large number of records (maybe half a million) against the value from tblSetting but I would expect the value from tblSetting, once fetched, to be re-used whereas it *appears* SQL keeps going back and getting the record again. Even though tblSetting only has 20 or so records in it, this seems to have quite an impact on performance.

Will try to share the results of the Actual Execution plan - but today is my last day before going on a long vacation during which time I plan not to be thinking about this (or any other work related) issues smile.gif

--------------------
Paul (visit my page for Access tips, tricks and samples: pdtech.co.UK).
Go to the top of the page
 
PDTech
post Jun 21 2017, 05:31 AM
Post#9



Posts: 174
Joined: 8-August 07
From: Doha, Qatar


Just managed to test with the aggregation (Max) removed and the logical reads on tblSetting have dropped from over a million to just 18 smile.gif

Fantastic - thanks for the suggestion!

--------------------
Paul (visit my page for Access tips, tricks and samples: pdtech.co.UK).
Go to the top of the page
 
LPurvis
post Jun 21 2017, 06:17 AM
Post#10


UtterAccess Editor
Posts: 16,057
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Glad (and yet still surprised) that it helped to that extent. :-s
There's still that UDF to fall back on (with a simpler parameter :-p).

But enjoy your holidays, well earned I have no doubt. (But sitting, relaxing with a drink thinking about databases ain't such a bad thing. :-p)

Cheers! cheers.gif

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th September 2017 - 06:13 AM