Performance for different users

[Paul Haboeck]

Hallo

I have the problem, that the performance between a 'normal SQL user' (=
verry slow) and 'sa' (normal performance) is verry different.

I open a form in my ADE Project an all data in this form are transfered vom
SQL Server to ADE. The data are produced by a stored procedure.

Then I move from one record to the next (visible) record in the form:

sa: no data transfer from SQL Server (shown in SQL Profiler)
normal user: a lot of reads happens (shown in SQL Profiler)

why is the access to the SQL Server different for 'sa' and 'normal user'?

Thanks for answers

Paul

 

[Sylvain Lafontaine]

This problem will show up if you are not using fully qualifiying names -
such as dbo.table1 instead of just table1 in your queries/sp/UDF. Using a
mix of dbo. and other user names for your schema is also a good recipe for
this kind of behavior.

Rewrite your SP, update the statistics on your tables and clean your
procedure cache should be OK. Thinking about using the WITH RECOMPILE
option and using intermediate variables to avoid parameter sniffing from
SQL-Server could also possibly - but not necessarily - be good ideas.