Link to home
Start Free TrialLog in
Avatar of syoffe
syoffeFlag for Israel

asked on

ms access list box based on SP does not display values (possibly due to timeout on the query)

Dear Experts,
I have a listbox with "row source" as a stored proc in SS DB. such as :
exec MyProc 1234
I build the string and set the property in VBA.

the SP takes almost 4 minutes to execute. (executes well from Management Studio)
after about 1 minute the control returns to access with no result.
I suspect timeout, I changed the "general timeout" on the connection dialogue box to 300
and also the "connection timeout" 300 as well.

please help
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

How long does it take the SP to execute in Management STudio?
Avatar of syoffe

ASKER

about 4 minutes
I would examine the SP and see if the performance can be improved.
4 minutes for a SP which gets the rowsource for a listbox does not sound reasonable.

Is it possible for you to post up the SP code?
Avatar of syoffe

ASKER

the SP key/id according to which data is retrieved is encrypted... that is why it is taking so long.
the code is something like :

ALTER Procedure [dbo].[dm_UI_FindPersonID]
      @ID as int
as
set nocount on

declare @sql_str nvarchar(4000)

set @sql_str = ' use MyDB
                                      
                OPEN SYMMETRIC KEY MySymmetricKey
                DECRYPTION BY CERTIFICATE MyCert ' +

                        ' SELECT F_ID, a.id, family, first, city, street, house_number ' +
                        ' from MyDB.dbo.pop a, MyDB.[dbo].[dm_pop_private] b ' +
                        ' where a.dm_id = b.dm_id ' +
                        ' and cast(DecryptByKey(b.[ID_encrypted]) as varchar) = ' + cast(@ID as varchar) +

                        ' CLOSE SYMMETRIC KEY MySymmetricKey'

exec sp_executesql @sql_str
How long does it take if you just run the SELECT on it's own IN SSMS for one @ID?
Avatar of syoffe

ASKER

about 4 minutes there are about 8000000 rows in the table
Avatar of syoffe

ASKER

if we assume 4 minutes is ok (not that it is but just for the discussion) what can I do with the form to be able to wait for so long ?
I just think that a form is the user interface and the user waiting for 4 minutes for something just doesn't sound right to me.
There is a DoEvents function but not sure if it will help here
There is also the possibility of implementing 'threading' in VBA - so one the execute of the proc is issued in one thread and the interface is kept updated in another thread.

But I still think it is worth analysing what is taking the time in the proc -
Like I have a table with 2,000,000+ records and if I pass in a single ID it returns the record in less than a second - so even if it had 8,000,000 records this would still possibly be 4 seconds at most.

It would be good if we could establish in your case that it is definitely the DecryptByKey function that is costing the time.

Would it be possible to do a similar test case SELECT but taking the DecryptByKey out of the equation.

What is behind DecryptByKey - Is it a t-sql scalar function.

A possibility may be to implement this DecryptByKey using CLR\assembly as it may perform faster
Avatar of syoffe

ASKER

Hi and thank you for your help.
I am almost certain it is the decryption which is taking time as it is a DB structure we implemented recently (encryption of some of the columns) prior to that with unencrypted data and the key column used in an index, the proc was returning within fraction of a second...
DecryptByKey is a built in tsql function to decrypt column data that was encrypted using SqlServer internal encryption. http://msdn.microsoft.com/en-us/library/ms181860.aspx

btw I don't think doEvents or threading will help in this case as it is not the UI freezing which is the problem. it is the timeout (I guess) that is raised on the client side that actually stops the execution of the proc.
Put together the most basic test case where DecryptByKey is called for only one record - something like

SELECT DecryptByKey(b.[ID_encrypted])
FROM MyDB.[dbo].[dm_pop_private] b
WHERE b.SomeUniQueIdentifier = <SingleID>

and let us know how long this takes to execute
ASKER CERTIFIED SOLUTION
Avatar of syoffe
syoffe
Flag of Israel 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 syoffe

ASKER

I found a technique to make the SP run faster. it does not directly address the question I raised but will however solve the issue.