| Author |
need to have a quick accessible variable per connection in MS SQL 2000
|
|
| digitalm 2005-02-25, 4:03 pm |
| Is there any way to store a quick accessible integer variable for the
MS SQL session? The purpose is to use it in a view's "where"
expression. In current realization we made a table in DB to store
values for each @@spid. In this mode the benchmark result is 6 minutes
for some task.
If we put a constant to the "where" clause then we get a 3:20 minutes
(this is the result we are trying to get close to) for the same task.
Then we developed an extended stored procedure (DLL) for the MSSQL
written in MSVC6 which implements access to a static integer array,
with an index of @@spid. The result was 17 minutes (seems
unbelievable).
In common we need to store the only 1 bit of information per user
connection. We tried using a last bit of @@TEXTSIZE variable, but we
don't have full control of the application and can only modify stored
procedures, functions ad triggers in database. So we can't modify the
global value of @@TEXTSIZE. Is there any way to set the global value
of @@TEXTSIZE variable? Can we get an access to it through extended
stored proc? Any suggestions will be appreciated.
| |
|
| check the SET CONTEXT_INFO in BOL, it might be what you're looking for.
dean
"digitalm" <dm@omsk.edu> wrote in message
news:98980055.0502250857.133c387d@posting.google.com...
> Is there any way to store a quick accessible integer variable for the
> MS SQL session? The purpose is to use it in a view's "where"
> expression. In current realization we made a table in DB to store
> values for each @@spid. In this mode the benchmark result is 6 minutes
> for some task.
>
> If we put a constant to the "where" clause then we get a 3:20 minutes
> (this is the result we are trying to get close to) for the same task.
>
> Then we developed an extended stored procedure (DLL) for the MSSQL
> written in MSVC6 which implements access to a static integer array,
> with an index of @@spid. The result was 17 minutes (seems
> unbelievable).
>
> In common we need to store the only 1 bit of information per user
> connection. We tried using a last bit of @@TEXTSIZE variable, but we
> don't have full control of the application and can only modify stored
> procedures, functions ad triggers in database. So we can't modify the
> global value of @@TEXTSIZE. Is there any way to set the global value
> of @@TEXTSIZE variable? Can we get an access to it through extended
> stored proc? Any suggestions will be appreciated.
| |
| Dmitriy Lapchik 2005-02-25, 9:01 pm |
| It works. But result is rather slow.
The only way to get back the value stored in CONTEXT_INFO is to perform
"select context_info from master.dbo.sysprocess where spid=@@spid" and
arrange some works with huge datatype - varbinary(128). After some
optimizations, the best result we've got is 21:54 minutes for the same
task.
The only difference between the current solution based on the additional
table is that in case of using CONTEXT_INFO takes much CPU time on
varbinary type managing.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
| |
| Dmitriy Lapchik 2005-02-26, 8:58 am |
| Success!
We attached an external DLL to an application and executed "SET
TEXTSIZE" using an application's ODBC descriptor in the same session.
Now task execution takes 3:20 minutes.
There was an interesting thing. If we put @@TEXTSIZE variable into
view's expression inline instead of using UDF we get 5:00 minutes result
due to bad SQL optimization.
Finally, I must say that MS SQL has a very poor architecture.
Anyway big thanks for your help!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
|
|
|
|