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 1516 ji**********@countrywide.com (Jim Geissman) wrote in message news:<b8**************************@posting.google. com>... 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
You don't give any information about your version of MSSQL, and the
client library you're using, but you may be able to set a suitable
timeout period on the client side. Alternatively, look at the "query
governor cost limit Option" in Books Online - this terminates queries
that run for more than a given number of seconds.
Simon ji**********@countrywide.com (Jim Geissman) wrote in message news:<b8**************************@posting.google. com>... 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
See "remote query timeout Option" in the help text. However, relying
on this may cause inconsistent bahaviour.
This design pattern can also lead to heavy load on your database.
As a suggestion, have two separate sets of queries, one that assumes
good data (should be much quicker which you want to use most times?)
and one that may have incorrect data (will be slower, but not used
very often). In you screen have a checkbox to indicate what search
option to use. Alternatively perform better validation on the data
before submitting the form.
That sounds interesting. I'll look into it. You don't give any information about your version of MSSQL, and the client library you're using, but you may be able to set a suitable timeout period on the client side. Alternatively, look at the "query governor cost limit Option" in Books Online - this terminates queries that run for more than a given number of seconds.
Simon This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Michele Simionato |
last post by:
There are situations in which you have to setup a faily sophisticated
environment before running your tests. This may result in a long
startup
time. In my case (using PloneTestCase) the time taken...
|
by: Nick Craig-Wood |
last post by:
I'm trying to avoid using shell metacharacters in os.popen in a portable
fashion.
os.popen() only seems to take a string as the command which would need
tricky quoting.
os.popen2() can take a...
|
by: Eloff |
last post by:
This is not really Python specific, but I know Python programmers are
among the best in the world. I have a fair understanding of the
concepts involved, enough to realize that I would benefit from...
|
by: kermit |
last post by:
I asp.net pages that run on an intranet IIS server. Some op the pages use
XLM DOM doc.Load(sPath) to open and parse a XML file. Every time the
doc.Load(sPath) executes in IE6 a warning message...
|
by: Frank-René Schäfer |
last post by:
-- A class needs to have N members according to N types
mentioned in a typelist (possibly with one type occuring more than
once).
-- The classes should be generated **avoiding** multiple...
|
by: Bob Nelson |
last post by:
It's been a long time since I've posed a query here on c.l.c. My work
environment evolved to primarily C++ and Perl with very little C, so I've
forgotten quite a lot over time.
This revisits the...
|
by: Amir Michail |
last post by:
Hi,
Trying to open a file for writing that is already open for writing
should result in an exception.
It's all too easy to accidentally open a shelve for writing twice and
this can lead to...
|
by: steve.j.donovan |
last post by:
Hi guys,
We have the following macro:
#define NEXT(type,p) (*((type*)(p))++)
It provides a way to poke variable sized data into an array of pcode
for a simple VM.
e.g,
|
by: robert.waters |
last post by:
Hello,
I have been experiencing crashes and code corruption in my project
(vbe6.dll; a decompile fixes the corruption); for the life of me I
cannot figure out why, and I can't pin down the...
|
by: =?Utf-8?B?Y2hlY2tyYWlzZXJAY29tbXVuaXR5Lm5vc3BhbQ== |
last post by:
I have a site which I secure with forms authentication. When the user's
sign on and hit one of the secure pages, I have this line in my code to
ensure that the browser does not cache the page;...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |