How much time does a query take?

Hello!
First of all, please excuse my (probably) bad english! :-/
My question is, how much time goes by until a query to a database is
finished? Is it fast enough for a query every 0.5 second?
In my application it's not usual that every 0.5 to 1 second a query is sent,
but it can happen.
I hope you can understand my concern and maybe can help me!
Greetings,
Christian
Am Fri, 10 Mar 2006 09:07:51 +0100 schrieb chris.lo:

> Hello!
> First of all, please excuse my (probably) bad english! :-/
> My question is, how much time goes by until a query to a database is
> finished? Is it fast enough for a query every 0.5 second?
> In my application it's not usual that every 0.5 to 1 second a query is sent,
> but it can happen.
> I hope you can understand my concern and maybe can help me!
> Greetings,
> Christian
Do you know what you want to ask? We have no information about the hardware
you use, how many users at the same time, how much data you have, what
queries are executed against the database, which funktions, triggers,
stored procedures are involved ...
So i can only say: if you use the same hardware as i and the same queries,
then it is fast enough :-)
bye, Helmut
|||Hello Helmut!
Of course you are right! I can't expect accurate answers without accurate
details!
So, here we are:
The MSDE is running on a P4 with 1.6 GHz and 512 MB Ram. On this PC there's
not only the MSDE running, but also a visualisation for a part of a
production plant. We plan to install a extra PC only for the MSDE, but that
depends on our customer...
At the moment there can be 10 users max "online". The amount of data depends
on the different Views which the user can select. I can't tell you that
amount in detail...

> which funktions, triggers, stored procedures are involved ...
I'm not THAT experienced in databases, but I can tell you that we didn't use
that things. (maybe the system uses that automatically?!)
Well, alltogether I can't make a proper description of what is happening in
detail. Maybe I tell you what I'm planning to do and maybe then you can tell
me whether is it possible or not:
Our application receives Messages from several PLCs. This messages tell our
app some info about the plant the PLC is controlling. Mostly the messages
tell our app when a fault (in the plant) occured und when it went away. Also
with this messages the app can count the produced parts (parts of a car) and
how long every single part took to be finished.
The message is represented by a 32-Bit number, an additional timestamp a
some flags. This numbers are not very informative, of course. So our
application has to lookup this number in a list and pick out the appropriate
description. This description can be seen by the user.
So far, so good. This mechanism is allready implemented in our application,
but I don't like it because it's extremely inflexible. So I plan to do it a
different way. I wanted to create the list with the numbers and description
as a table in the database. And every time a message was received from a
PLC, the app would execute a query against the database to retrieve the
description.
Like I wrote in my first question, in that way there can be a query every
0.5 second. And now I wonder whether this method is practicable or not. I
have concerns that this way will maybe slow down the whole application...
What do you thing? Should I do this? Maybe you wonder why I don't change the
mechanism in our app itself. Well, that belongs to internal functionality,
to which I don't have access.
I'm looking forward to some helpfull informations or opinions.
Greatings,
Chris
"helmut woess" <hw@.iis.at> schrieb im Newsbeitrag
news:byh92sdxklyh$.bcfu0dq0a5y9$.dlg@.40tude.net...
> Am Fri, 10 Mar 2006 09:07:51 +0100 schrieb chris.lo:
>
> Do you know what you want to ask? We have no information about the
> hardware
> you use, how many users at the same time, how much data you have, what
> queries are executed against the database, which funktions, triggers,
> stored procedures are involved ...
> So i can only say: if you use the same hardware as i and the same queries,
> then it is fast enough :-)
> bye, Helmut
|||hi,
chris.lo wrote:
> The message is represented by a 32-Bit number, an additional
> timestamp a some flags. This numbers are not very informative, of
> course. So our application has to lookup this number in a list and
> pick out the appropriate description. This description can be seen by
> the user.
> So far, so good. This mechanism is allready implemented in our
> application, but I don't like it because it's extremely inflexible.
> So I plan to do it a different way. I wanted to create the list with
> the numbers and description as a table in the database. And every
> time a message was received from a PLC, the app would execute a query
> against the database to retrieve the description.
> Like I wrote in my first question, in that way there can be a query
> every 0.5 second. And now I wonder whether this method is practicable or
> not. I have concerns that this way will maybe slow down the whole
> application...
I do not know your actual schema, but your words seems to tell you actually
have a normalized one...
personally I'd not make a lookup every time a message is received, as you
probably already have all needed pieces of information to lookup only at
"reporting time" whenever it is performed/needed.. without that lookup you
preserve your schema in a better normalized manner and you preserve system
resources..
when reports are needed, you can perform your join and present data in the
human readeble way as all encoded values will be there resolved to their
human "descriprions"...
if you keep your batch operations as little as possible, you should decrease
possibility to hit the internal MSDE limit of 8 (5 + 3 system batches)
concurrent workloads, but, more important, you keep a normalized schema
where your foreign key constraints are resolved only on a "when needed"
basis..
obviously, IMHO
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello Chris,
there are still many dark horses and i think it is not possible to give you
a warranty that this will work or will surely not work.
A dangerous part is MSDE and 10 concurrent users. You can have 10
concurrent users, but if some requests arrives nearly at the same time,
then MSDE starts to insert wait cycles and then it comes very fast to a
bigger delay.
This has gone with SQLExpress, so if you have only MSDE and no real
SQL-Server then it would be better to use SQLExpress.
And because visualisation can be very ressource intensive, i would not do
both on the same machine.
My suggestion: a new PC with 1 Gb Memory and a fast harddrive, Windows 2K
as OS and SQLExpress. Under normal circumstances this should be enough.
bye,
Helmut
|||Hello!
Thank you both for your answers und opinions! I think I simply will test it,
and then I can decide whether I will do it that way or not...
Greetings,
Christian