Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

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
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Depending on how you select your record, but tables have a primary key, and so can identify individual records quickly. A view does not have a PK, nor does it inherit any primary key properties.

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
Do you have an index(s) on the field(s) you are using for criteria ?
Avatar of bfuchs

ASKER

@Kelvin,
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
Avatar of bfuchs

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
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
Avatar of bfuchs

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
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
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?
Avatar of bfuchs

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
ASKER CERTIFIED SOLUTION
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

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
Avatar of bfuchs

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.)
Debug.Print Now
    SelectEmployeeFromList 
Me.RecordSource = "proc_employeesfrm"
Me.InputParameters = "IEmpID int = Forms!Employeesfrm.EmployeeID"
    Me.Requery
Debug.Print Now & " - 2"

Open in new window


Thanks,
Ben
Try replacing line 3 with SELECT * FROM yourtablename WHERE IEmpID int = Forms!Employeesfrm.EmployeeID
Delete line 4 then retest
Avatar of bfuchs

ASKER

Now I get the attached.

Thanks,
Ben
Untitled.png
Ah!, copied and pasted from your parameters property. It should be WHERE IEmpID = Forms!Employeesfrm.EmployeeID
Rather

"SELECT * FROM yourtablename WHERE IEmpID " & Forms!Employeesfrm.EmployeeID
@ 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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of bfuchs

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
Avatar of bfuchs

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!
Avatar of bfuchs

ASKER

@Kelvin,

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
Avatar of bfuchs

ASKER

Did you look at my second attachment?

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
Avatar of bfuchs

ASKER

USA:)
This is the time I have to do research..

Thanks,
Ben