bfuchs
asked on
Form performance table vs. query
Hi Experts,
I have a form that is bound to a SQL table and uses the attached SQL as record source.
Now if I use a view instead of a table, which has far less fields then the table and should work work faster, but its the opposite, takes longer to bring up a particular record.
FYI- the only difference from view and the table is that not all fields are included, and view is not sorted.
What can be done?
Untitled.png
I have a form that is bound to a SQL table and uses the attached SQL as record source.
Now if I use a view instead of a table, which has far less fields then the table and should work work faster, but its the opposite, takes longer to bring up a particular record.
FYI- the only difference from view and the table is that not all fields are included, and view is not sorted.
What can be done?
Untitled.png
Do you have an index(s) on the field(s) you are using for criteria ?
ASKER
@Kelvin,
@Joe,
Thanks,
Ben
I prefer to build a select statement on the table based on the selection criteria you use and use that as the recordsource setting it via code and requerying the form. You are then bound to just one record..This is the technique used here a well (see attached OP).
@Joe,
Do you have an index(s) on the field(s) you are using for criteria ?Yes, ID used as param is the PK in my table.
Thanks,
Ben
With the view, are you creating a linked table based on the view, and then binding to it?
If so, create a unique index in access on the PK field of the table that is based on the view.
Kelvin
If so, create a unique index in access on the PK field of the table that is based on the view.
Kelvin
ASKER
@Kelvin,
Sorry for not specifying it at the beginning, Its an ADP project.
Do you think I can make that view an indexed view? never did that, but I know its kind of complicated..
Thanks,
Ben
Sorry for not specifying it at the beginning, Its an ADP project.
Do you think I can make that view an indexed view? never did that, but I know its kind of complicated..
Thanks,
Ben
Ah, an Access Project. Then throw everything I said out the window!
It's a while since I worked with one of those - were great in their day.
They allow you to bind a form to a Stored Proc, so rather than a view (which can't have parameters), use a stored proc to return the columns you want from the table, with input paramenters being the selection parameters - this can be passed from a Project forms parameters property - the syntax of which I can't recall and is a bit picky!
Kelvin
It's a while since I worked with one of those - were great in their day.
They allow you to bind a form to a Stored Proc, so rather than a view (which can't have parameters), use a stored proc to return the columns you want from the table, with input paramenters being the selection parameters - this can be passed from a Project forms parameters property - the syntax of which I can't recall and is a bit picky!
Kelvin
ASKER
Not sure why a stored proc would be more efficient than the current SQL "SELECT * FROM view_Employeestbl WHERE (ID = ?)", however if you think so I can try play around with that & let you know..
Thanks,
Ben
Thanks,
Ben
The stored proc will do a select from TABLE (which has the PK), whereas you're currently doing SELECT from VIEW WHERE and the where isn't using the view that doesn't have a PK. The only way you'd find out for sure is to compare the SQL Server execution plans of the two (actual rather than estimated) and see where they differ.
Reading Execution Plans is an art of it's own and you'll find many books written on that alone!
Kelvin
Reading Execution Plans is an art of it's own and you'll find many books written on that alone!
Kelvin
A plain view (no joins, no sorting, grouping ordering) should only be marginally slower than a table. I would expect that you couldn't even tell the difference working from access.
A view should always be optimized for the process in which it is to be used. I..e don't select from a view that has joins which you don't need.
If you are finding that selecting from the view is noticeably slower than selecting from a table, then check whether the view is really suited for your need. Does it contain joins, sorting, or something else that requires extra work, that isn't needed for your selection?
A view should always be optimized for the process in which it is to be used. I..e don't select from a view that has joins which you don't need.
If you are finding that selecting from the view is noticeably slower than selecting from a table, then check whether the view is really suited for your need. Does it contain joins, sorting, or something else that requires extra work, that isn't needed for your selection?
ASKER
@Kelvin,
Just got a chance to test your suggestion, see attached what setting I use and whats the error msg I get when opening form w/o specific records, which in that case I would want a blank screen so users can add new records, the way it currently works with the select * from table/view.
Thanks,
Ben
Untitled.png
Untitled1.png
Just got a chance to test your suggestion, see attached what setting I use and whats the error msg I get when opening form w/o specific records, which in that case I would want a blank screen so users can add new records, the way it currently works with the select * from table/view.
Thanks,
Ben
Untitled.png
Untitled1.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If you're doing both adding and editing, drop the SP idea..Actually since I'm dealing with a huge table with hundreds of fields (dont ask me why..), I was trying to use a view that would only bring up the necessary fields, and would be easier to manage them with SSMS query designer, however if I need to write them manually, then Its no difference where I do it, on SP or on the forms module..
@Anders Ebro,
As mentioned, the view is the same exact data as the source table, no joins or sorting involved, only purpose of it is to include fewer fields than the select * from table.
Thanks,
Ben
ASKER
@Kelvin,
Just fyi, I tried opening the form while bound to the table and then running the below while selecting the record, and it doubled the time it took to bring up the record. (from 4-5 sec to 9 sec.)
Thanks,
Ben
Just fyi, I tried opening the form while bound to the table and then running the below while selecting the record, and it doubled the time it took to bring up the record. (from 4-5 sec to 9 sec.)
Debug.Print Now
SelectEmployeeFromList
Me.RecordSource = "proc_employeesfrm"
Me.InputParameters = "IEmpID int = Forms!Employeesfrm.EmployeeID"
Me.Requery
Debug.Print Now & " - 2"
Thanks,
Ben
Try replacing line 3 with SELECT * FROM yourtablename WHERE IEmpID int = Forms!Employeesfrm.Employe eID
Delete line 4 then retest
Delete line 4 then retest
ASKER
Ah!, copied and pasted from your parameters property. It should be WHERE IEmpID = Forms!Employeesfrm.Employe eID
Rather
"SELECT * FROM yourtablename WHERE IEmpID " & Forms!Employeesfrm.Employe eID
"SELECT * FROM yourtablename WHERE IEmpID " & Forms!Employeesfrm.Employe
@ Kevin ... just curious. I might be missing something, but unless SQL Server is radically different that Access and since a View in SQL Server is more or less equivalent to a stored query, I'm not seeing why a View would be slower than directly connecting to a table?
Especially since way few fields are being passed over the network?
Especially since way few fields are being passed over the network?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This is MY view on it. A view in SQL Server is much like a stored query in Access EXCEPT that it does not and cannot have any parameters - so it sees all the rows. You then pass a where statement on that to filter - but you've lost the PK and any other indexes as they're on the table.
I don't believe that the number of columns makes much difference, whereas there's a performance hit on the rows.
A stored proc can "mimic" a view and does allow the use of parameters - as do User defined Table functions.
Kelvin
I don't believe that the number of columns makes much difference, whereas there's a performance hit on the rows.
A stored proc can "mimic" a view and does allow the use of parameters - as do User defined Table functions.
Kelvin
ASKER
@Kelvin, @Joe,
Currently when I time the Select * from MyTable I get 4 sec while Select * from MyView I get 5 sec, my goal is to get it to 3..but definitely not above 4.
Will try both suggestions and see what's best.
Thanks,
Ben
Currently when I time the Select * from MyTable I get 4 sec while Select * from MyView I get 5 sec, my goal is to get it to 3..but definitely not above 4.
Will try both suggestions and see what's best.
Thanks,
Ben
ASKER
At the moment I got that to work at 4 sec.
In case you do have a way to accelerate this further, let me know and I will open a new thread.
Thank you experts!
In case you do have a way to accelerate this further, let me know and I will open a new thread.
Thank you experts!
ASKER
@Kelvin,
FYI-
Thanks,
Ben
Untitled.png
Untitled1.png
FYI-
The only way you'd find out for sure is to compare the SQL Server execution plans of the two (actual rather than estimated) and see where they differ.See attached how they both use the PK index, in addition I see the view has less on duration column then the table, the rest of the columns appear to be the same.
Thanks,
Ben
Untitled.png
Untitled1.png
Interesting, and somewhat curious.
Rather than profiler, I prefer to see the Execution plan. If you have access to the SQL Server Management Studio, create the query, but before you run it, look in the Query Menu and select Display Actual Execution Plan (or press Ctrl M). Then run the query - you'll get an execution plan tab down with the results. It'll break down what SQL was doing and what percentage of the total time was spent on what.
Kelvin
Rather than profiler, I prefer to see the Execution plan. If you have access to the SQL Server Management Studio, create the query, but before you run it, look in the Query Menu and select Display Actual Execution Plan (or press Ctrl M). Then run the query - you'll get an execution plan tab down with the results. It'll break down what SQL was doing and what percentage of the total time was spent on what.
Kelvin
ASKER
Did you look at my second attachment?
Thanks,
Ben
Thanks,
Ben
Sorry, thought I had but hadn't. That really doesn't explain the behavior you were getting in the apd. Mind you, when I used to work with them, I did observe strange things from time to time which most frustrating to say the least.
Ben, you time with questions seems very similar to my time zone (unusual for EE). Where in the world are you located? I'm in New Zealand.
Kelvin
Ben, you time with questions seems very similar to my time zone (unusual for EE). Where in the world are you located? I'm in New Zealand.
Kelvin
ASKER
USA:)
This is the time I have to do research..
Thanks,
Ben
This is the time I have to do research..
Thanks,
Ben
I try and avoid binding forms to tables or views from SQL Server - just too slow. I prefer to build a select statement on the table based on the selection criteria you use and use that as the recordsource setting it via code and requerying the form. You are then bound to just one record and it is very fast.
Kelvin