The C++ application calls the database to look up property data. One troublesome query is a function that returns a table, finding data which is assembled from four or five tables through a view that has a join, and then updating the resulting @table from some other tables. There are several queries inside the function, which are selected according to which parameters are supplied (house #, street, zip, or perhaps parcel number, or house #, street, town, city,...etc.). If a lot of parameters are provided, and the property is not in the database, then several queries may be attempted -- it keeps going until it runs out of queries or finds something. Usually it takes ~1-2 sec for a hit, but maybe a minute in some failure cases, depending on the distribution of data. (~100 mil properties in the DB) Some queires operate on the assumption the input data is slightly faulty, and take relatively a long time, e.g., if WHERE ZIP=@Zip fails, we try WHERE ZIP LIKE substring(@Zip,1,3)+'%'. While all this is going on the application may decide the DB is never going to return, and time out; it also seems more likely to throw an exception the longer it has to wait. Is there a way to cause the DB function to fail if it takes more than a certain amount of time? I could also recast it as a procedure, and check the time consumed after every query, and abandon the search if a certain amount of time has elapsed. Thanks in advance, Jim Geissman
3. Time outs when accessing dimension
I have a Account Name dimension that groups Account Names. There over 200,000 account names, hence, the grouping. I created a view with indexed to help with pulling the account names but I don't see a difference. It time out both in the Cube and Excel Pivot table when I added the Account Name dimension. If I am successful in adding the dimension to the data area, it times out when I click the drop down menu to choose one. Any suggestions to improve the performance of this dimension? I also set Top level only in the dimension settings but no change.
4. Time-outs, i have to reset the sql server to generate again in - SQL Server
5. Only getting time outs when running a query from .net app
I've got a weird problem where when I run a certain query from within any .net application it will eat a LOT more CPU cycles and eventually time out. When I run the same query from within SQL Server Management Studio the query completes in under a second. Using profiler I see that it uses 28,734 cpu cycles before timing out and when run from SSMS it uses 125 cycles before completing successfully. This behavior happens when I run the query from within a stored procedure or run it on its own. I also tried using various accounts and forms of authentication and none of that had any effect on it. I tried connecting with .net 1.1 and 2.0 but got the same results. The fact that profiler shows different CPU usage depending on whether or not I'm using .net causes me to think this is a sql server issue. Does anyone have any ideas what is causing this or what I could check out? I am using SQL Server 2005 and have not been able to repro on SQL 2000 yet.
6. Monitor Data Access Speeds & Time-Outs - SQL Server
SQL Server has (in its sp_configure) the option to set "remote query timeout" and in Query Analyzer it can set the "Query time-out" option in seconds. My first question is: Does the "remote query timeout" option only handle queries from remote servers or does it work on queries directly conncected to the local SQL Server? Meaning, does the "remote query timeout" option work like QA's "Query time-out" on the server? My next question is: Depending on the answer from above, is there a way to configure the max time for a given query? The last question: If SQL Server does not have a method for the above question, is there a third party app that can limit the amount of time a query can take on the SQL Server? Bill