Database Application not useing 100% of CPU

[TheMillionDollarMan]

Hi.

I have a database application that doesn't use all of my CPU. It does rely on data from a database that sits on our network but the NIC shows me that the Ethernet card is not utilized to its full capacity.

So I am assuming that the application gets its data as fast as it needs it.

What other bottle necks (besides the network) could be causing only a partial usage of the CPU?

Calling the C++ dll means that my machine will only use 60-80% of the CPU when I call the same dll from VB it only uses 50-30%. Why would VB be slower?

Thanks

 

[Cagliostro]

noneffective queries

Ion Filipski

 

[TheMillionDollarMan]

Thanks Ion.
Its an area we need to look into some more thats for sure.

 

[williamu]

Hi,

I don't see what the problem is here. Surely this is what you want, by NOT hogging the processor. Also, unless you're returning lots of rows the network in theory wouldn't even break in to a sweat.

To me your understanding of a bottleneck is back to front. A bottleneck IS when the CPU or all BandWidth is being leeched, by a single process. And as a result nothing else can get processed or over the network.

The additional overhead between VB and C++ would be in the runtime interpretation of the VB code. Hence the additional use of the CPU.

If you want to minimise CPU and Network usage even more I'd suggest that you look into using Stored Procedures. These have the advantage of being precompiled on the DB and as a result do not need to be parsed by the SQL engine. All that's required is for the Parameters to be bound.

As a result you can reduce the size of the query passed over the network.

P.S. To Ion Filipski


William
Software Engineer
ICQ No. 56047340

 

[Cagliostro]

Small size of a query does not mean more efficiency. Efficiency in queries could be obtained by not using clause "union" and in where clause to not use nonindexed fields as much as possible. Also join criteria should be correctly specified.
For exampe
select * from table, table
is a very nonefficient query, because if it ias a small, 1000 rows table, the query will give 1000 * 1000 = 1000000.
correctly will be some good join criteria:
select * from table x1, table2 x2
where x1.aa = x2.bb

Ion Filipski

 

[williamu]

Ion,

I never said it did. If you've got a crappy query you've got a crappy query. I was referring to traffic on the network not the efficiency of the Query being used.





William
Software Engineer
ICQ No. 56047340

 

[Cagliostro]

How about this part of the original post:
>What other bottle necks (besides the network) ...
???
The traffic will be always the same, if you use java or c++ or VB or any other programming language. Moreover, the traffic will be the same if you use ADO, OCI, OO4O, ODBC or any other DB API's. Moreover, the traffic is the same if you use DCOM, TCP, RPC, CORBA, SOAP or any other TCP based network protocols. So, in the cases "besides network" there could be only SQL efficiency or client side processing of received data. There could be for example a problem with ODBC driver if there are small frequently queries executions.

Ion Filipski

 

[williamu]

I'm not to sure what you mean by "The traffic will be always the same...". However, the amount of traffic (packets required) will be less when using a view, for example:

If you have sommit like :

CREATE VIEW UPDATE_PROGENY (ID, Firstname, Others, Sex, Course, DOB, dtMeasles, dtMumps, dtRubella, dwMeasles, dwMump, dwRubella) AS
SELECT ID, Forename, Initials, Gender, Course, DOB, (SELECT Vaccinations.Datepaid AS dtMeasles FROM Vaccinations WHERE ((Vaccinations.Progeny = Progeny.ID) AND (Vaccinations.Vaccine = 1))), (SELECT Vaccinations.Datepaid as dtMumps FROM Vaccinations WHERE ((Vaccinations.Progeny = Progeny.ID) AND (Vaccinations.Vaccine = 2))), (SELECT Vaccinations.Datepaid as dtRubella FROM Vaccinations WHERE ((Vaccinations.Progeny = Progeny.ID) AND (Vaccinations.Vaccine = 4))), (SELECT Vaccinations.Charged AS dwMeasles FROM Vaccinations WHERE ((Vaccinations.Progeny = Progeny.ID) AND (Vaccinations.Vaccine = 1))), (SELECT Vaccinations.Charged AS dwMumps FROM Vaccinations WHERE ((Vaccinations.Progeny = Progeny.ID) AND (Vaccinations.Vaccine = 2))), (SELECT Vaccinations.Charged AS dwRubella FROM Vaccinations WHERE ((Vaccinations.Progeny = Progeny.ID) AND (Vaccinations.Vaccine = 4))) FROM Progeny ;

Can be replaced with SELECT * FROM UPDATE_PROGENY

This obviously requires less packets to send across the network than the latter.

The same applies if you're using a Procedure to do a big update.


William
Software Engineer
ICQ No. 56047340

 

[Cagliostro]

>williamu (Programmer)
>I'm not to sure what you mean by "The traffic will be >always the same...".
>.....
>Software Engineer
>ICQ No. 56047340

>IonFilipski
>....
>....", if you use java or c++ or VB or any other programming language...." (no matter of what kind of language!!!)
stupid to explain that again. You should read entirely the phrase before responding to it.

Ion Filipski

 

[TheMillionDollarMan]

thank you both...I think Ion nailed it on the head with the comment...

>>there could be only SQL efficiency or client side processing of received data

Which is the problem I think.