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
> Using Worktable Instead Of View As Join In SQL View, Any Versions    
 
   
cocoflipper
post Mar 12 2018, 02:34 PM
Post#1



Posts: 1,144
Joined: 11-August 03
From: Denver - CO


Hi all,

I've been having trouble optimizing a view that has a number of other views joined to it. I am working with around 3-4,000 records, so nothing overwhelming. The main view was taking around 6-10 seconds - too slow in my opinion.

In order to improve the speed of the view, I looked at all the joined tables and views in this view. The main view included a join to a particularly slow view. So, in main view instead of joining to this slow view I switched to using a worktable with the same information. What I set up was a call to run a stored proc in my Access code which updates worktable based on the slow view (copies over all the same fields into a worktable), which is joined the same way to the main view as the prior, slow-running view was. The result is that main view run a lot faster, and pulling up the data in Access is a lot faster.

I don't really feel like this is a best practice, but it certainly runs faster and gives the same info. Did I do something that sends me to the SQL Server dungeon? Anyone out there want to weigh in on saying this is OK, or chastising me. Please include the "why" as well.

Thanks!

--------------------
"Effort only fully releases its reward after a person refuses to quit."
- Napolean Hill, author
Go to the top of the page
 
MadPiet
post Mar 12 2018, 02:40 PM
Post#2



Posts: 2,427
Joined: 27-February 09



Sounds okay. There are times when writing some of the data from a table to a temporary table and then joining to it performs better than other options. If you have time (and you may not), you might want to look at the nested views.
Go to the top of the page
 
LPurvis
post Mar 13 2018, 07:51 AM
Post#3


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


Hi

>> a call to run a stored proc in my Access code which updates worktable based on the slow view (copies over all the same fields into a worktable), which is joined the same way to the main view as the prior

So, just to clarify, you're required to run the procedure first (and every time) before executing the view and aren't concerned about any changed possible in the interim (however unlikely that may seem).
I'm not a fan of that two stage preparation. You'll need to document it well (even for yourself) to know that it is required.
Since you're executing a stored procedure anyway, can't you just put the full data retrieval in there? Call it via a Passthrough if you need to use the data in the UI and don't want to or can't bind to a recordset.

Alternatively, use a Function instead of a procedure. The function can use a similar work table (is it a temporary table rather than a permanent work table - as surely concurrent users are an issue?). Or, perhaps, a table variable and join to it in the function.
You could call that function from a view and link the view as normal if you prefer connecting/binding in that way.

That said, I'm curious why a query of just a few thousand rows is taking so long to execute. Are you sure the views are as efficient as they can be?

Cheers

--------------------
Go to the top of the page
 
Jeff B.
post Mar 13 2018, 12:06 PM
Post#4


UtterAccess VIP
Posts: 10,066
Joined: 30-April 10
From: Pacific NorthWet


I haven't noticed any mention of indexing (but I may be undercaffeinated this morning). ... especially on fields/columns used in joins ...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
cocoflipper
post Mar 13 2018, 12:32 PM
Post#5



Posts: 1,144
Joined: 11-August 03
From: Denver - CO


Hi Jeff B.

Just started looking into my indexing on this db yesterday...looking at these two articles, but feel free to suggest others:

Missing Index Script - SQL Authority

SQL Script to find the missing indexes

I ran these and implemented most of the suggestions, based upon what I think I need a the moment. I will continue to look into what the indexes are, especially, as you mention, on fields/columns used in the joins of my slow views.

For as much as I've been digging around SQL Server over the past 10 (?!) years, I feel like a nube, as far as optimizing....

--------------------
"Effort only fully releases its reward after a person refuses to quit."
- Napolean Hill, author
Go to the top of the page
 
cocoflipper
post Mar 13 2018, 12:58 PM
Post#6



Posts: 1,144
Joined: 11-August 03
From: Denver - CO


Hi LPurvis,

Yes, I do have to run the two stage process (run SP from a spot in the code in Access, then use the view). I am not a fan of this either, because I then have to make sure that for any query that uses the view I've already updated the worktable via the SP.

I supposed I could put the full data retrieval in the SP - I'm just not used to doing it now. I will look into how I could do that to present my data in Access.

The worktable is permanent, and I don't think there is a concurrent issue. Most times, this view is pulled up as a static recordset in a user generated query, so the next time it is run, if the SP runs to update the data, should have updated data without causing issues to the user. But...it just doesn't seem like it's a best practice, so I'm willing to get something more dynamic that doesn't have that 2 step process.

Lastly, here's the issue with the view that is slow.... it is on a person status transaction table, which will detail the transactions which change a person's status (active [type], inactive, etc.). I pull a few things together here:

- intake status info (date, type of status at intake), basically a MIN date of the person's values in the status table
- current status (date, type of status current), basically a MAX date of the person's values in the status table
- last inactive status (date, type of inactive status), basically a MAX date of the person's "inactive" values in the status table

This view, when attached to a view of the person's information view, slows things down. Any suggestions as to a way to optimize this type of view is appreciated.

...OK, maybe I was exaggerating a bit.... when I run the view in SSMS I can count to three with the main query using the view, and I can count to two with the main query using the worktable. When run in Access the difference is about the same using a pass thought query (2 seconds using the view, almost instantaneous using the worktable). However, attaching it to other queries in Access really slows things down. I'm trying to switch to pass throughs as much as I can for this Access db, but in the meantime I do have to make sure that users can do their work without staring blankly at my app as the views whir in the background.

Thanks for your input!

--------------------
"Effort only fully releases its reward after a person refuses to quit."
- Napolean Hill, author
Go to the top of the page
 
LPurvis
post Mar 13 2018, 02:14 PM
Post#7


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


Hi

>> when I run the view in SSMS I can count to three with the main query using the view
When you execute a query in SSMS, you can see the execution time in the status bar on the bottom right. (Next to where the number of returned rows is displayed :-)

>> Any suggestions as to a way to optimize this type of view is appreciated.
We'd really need to see the tables' schemas. You're attempting to pull out transactions which change someone's status, but we'd need to see how these tables interact.
Can you list those perhaps?

>> if the SP runs to update the data, should have updated data without causing issues to the user.
But it does it for all users, that's my issue and concern with concurrency. It's not specific to the current process / connection.

>> I could put the full data retrieval in the SP - I'm just not used to doing it now. I will look into how I could do that to present my data in Access.
and
>> I'm trying to switch to pass throughs as much as I can
There's your answer ;-).

But also, I did mention perhaps using a Function (rather than an SP) to both fill a table (temp table or table variable perhaps) with the status data and query against that and return the results to the view. (And you can link to the view just as you have been doing.)

Cheers

--------------------
Go to the top of the page
 
LPurvis
post Mar 13 2018, 02:20 PM
Post#8


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


Hi again.

I just noticed subsequently...

>> However, attaching it to other queries in Access really slows things down.
I'm sure you are, based on the earlier statements... but you are joining this view to other views... in a view on the server yes?
Not joining linked tables (linked to views) in the Access client?

Cheers

--------------------
Go to the top of the page
 
cocoflipper
post Mar 14 2018, 02:26 PM
Post#9



Posts: 1,144
Joined: 11-August 03
From: Denver - CO


Not in this case, but I'll admit that I've got a few in this db where I have linked tables (views) in Access joined in a query in Access. Those I am removing as I find them.

--------------------
"Effort only fully releases its reward after a person refuses to quit."
- Napolean Hill, author
Go to the top of the page
 
LPurvis
post Mar 14 2018, 02:33 PM
Post#10


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


OK cool. (Don't forget.... Function. :-)

--------------------
Go to the top of the page
 
cocoflipper
post Mar 21 2018, 01:00 PM
Post#11



Posts: 1,144
Joined: 11-August 03
From: Denver - CO


Hi LPurvis,

Over a hurdle of reporting, and beginning to dig back into this.... I have many questions, but one in particular relates to using a function as opposed to joining to another view in SQL Server. You seem, by your reminders, to favor using functions to create temp tables or table vars to return results. My specific question here is - Is that faster?

Say I want to filter a view by a population. What I have been doing is to create a view with that population, then join it to another view which may have the data I want. Are you saying that it would be faster to use a function to get this filtered population, instead of a view? Then, would I include that as part of the filter on the view that I want ?

For example, I have a list of people, and along with the person's record, I want to know if the have a certain condition. Currently, I have a people table and a condition table (which has an entry for condition and whether it is an active condition - not all people have an entry into this table). What I then have is a main view which has the people table, then I left-join that to another view which has the people ID and "Currently Has Condition". I then have a field that main view which displays whether the person has that condition ( using CASE WHEN (conditionview.peopleID) IS NULL THEN 'No' ELSE 'Yes' END). So, would you say that a function to get this list of people with a condition would be better than a static would, speed-wise.

I hope I am explaining that properly - really what I want to know is "would a function to get a list of IDs be better than a view to get that same list of IDs"

--------------------
"Effort only fully releases its reward after a person refuses to quit."
- Napolean Hill, author
Go to the top of the page
 
LPurvis
post Mar 21 2018, 02:33 PM
Post#12


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


Hi

Before jumping into that, I'll recap a bit.

>> a call to run a stored proc in my Access code which updates worktable based on the slow view (copies over all the same fields into a worktable), which is joined the same way to the main view as the prior
Your solution to achieve reasonable speed, was to store the results of a view in a work table. It was using the view directly that caused a performance hit. (I assumed perhaps because it was referenced more than once, or aggregated in some way?)
It was this action which made me think you want an all-in-one solution, call a single object, but be able to hold those heavy view records, without having to first execute a separate procedure.

We'd since confirmed:
>> but you are joining this view to other views... in a view on the server yes? Not joining linked tables (linked to views) in the Access client?
>> >> Not in this case, but I'll admit that I've got a few in this db where I have linked tables (views) in Access joined in a query in Access. Those I am removing as I find them.

So whatever was the issue with your view (whatever it is), holding the values temporarily and then querying against that improved your execution time.
Hence, the suggestion of a Function. The idea being that you could perform the necessary stages in the function, and call that function from a view so you can link to it from Access as you do mostly already.

So really my question back is: Do you not need the staging work table or is that still required?
(Another question might be what can be done to speed up the problem view, but we can leave that.)

If you are better off holding that problem view's data in a table and querying against that, you can accomplish the task in one using a function and no need for a (IMO slightly messy) precursory call to an SP.

As a rule, creating a function and calling that from within a view won't be faster then just creating a view. It's not some panacea solution I'm suggesting. It just seemed to lend itself to this situation.
And of course, the ultimate test for which is faster - test them both. :-p

Cheers

--------------------
Go to the top of the page
 
cocoflipper
post Mar 22 2018, 10:40 AM
Post#13



Posts: 1,144
Joined: 11-August 03
From: Denver - CO


Thanks LPurvis, for the insight. I'll include this advice in my thoughts as I dig through this app to come up with better, faster ways to pull data.

--------------------
"Effort only fully releases its reward after a person refuses to quit."
- Napolean Hill, author
Go to the top of the page
 
LPurvis
post Mar 22 2018, 01:13 PM
Post#14


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


thumbup.gif

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


Custom Search
RSSSearch   Top   Lo-Fi    22nd July 2018 - 04:08 AM