Problem With Performances
Apr 13, 2006
Hallo everbody,
I'm having a serious problem with my aspx pages. I
developed few pages that allow users to retrieve books information from
a database (MS SQL). The database table contains about 5000 records.
My queries are very simple, nevertheless the time necessary to perform them is extremely long.
I tried to set the time out of the data adapter to 0 and I removed the
debug mode (debug="true") from the web.config file. I'm still not able
to perform a query.
What is wrong with it? Is it something related with the database?
Any help is appreciated...
Thanks,
christian
View 5 Replies
Jun 15, 2008
Hello,
Lets consider two tables : CUSTOMERS and ORDERS.
I would like to know which of the following query is the fastest:
select * from customers C
where exists (select 0 from ORDERS O
where C.Name like 'A%' and O.Charged = 1)
select * from customers C
where exists (select 0 from ORDERS O
where O.Charged = 1) and C.Name like 'A%'
I don't want to use a JOIN clause. I just want to know if there is a difference of efficiency when the condition on C.Name is inside or outside the sub query.
Thank you in advance for any advices,
regards,
mathmax
View 2 Replies
View Related
Jun 16, 2008
Hello,
I would like to know which of the following query is the fastest:
select * from customers C
where exists (select 0 from ORDERS O
where C.Name like 'A%' and O.Charged = 1 and O.Customers_Id = C.Id)
select * from customers C
where exists (select 0 from ORDERS O
where O.Charged = 1 and O.Customers_Id = C.Id) and C.Name like 'A%'
Because the condition C.Name like 'A%' is inside the sub query, I'm wondering if it will be evaluated for each record of the Orders table. Does anyone know if there is a difference of efficiency when this condition is inside or outside the sub query ?
Thank you in advance for any advices,
regards,
mathmax
View 1 Replies
View Related
Jun 15, 2007
Hi to everyone!
I've just started to learn MS SQL Server 2005 and I hope you can give me some advices about what is the best design for the max performances between the two cases:
Case 1:
Single table, but not very scalable
Table name: GS
salary_id (primary key, int)
salary_description (varchar)
salary_cumulative_1 (int)
salary_cumulative_2 (int)
..
salary_cumulative_9 (int)
Case 2:
Two tables, this removes the limit of having only 9 salary_cumulative field, but will be performance worst? Keep in mind that this table(s) will be queried a lot the whole times when the user use the application.
(1) Table name: GS
salary_id (primary key, int)
salary_description (varchar)
(2) Table name: Cumulative
salary_id (primary key, int), foreign key
salary_cumulative (varchar)
Thank you a lot for your time!
ps: Can you also please tell me if using long (very long) names for the tables name will decrease the performances too?
Greetings,
Mirto
View 4 Replies
View Related
Apr 11, 2007
Hello,
I've written this stored proc, which have to collect records in one table in several databases. These tables contains very few records (1 to 10 max). In SQL Server standard edition, this stored proc is executed instantaneously. But in the Express, it could take 1 minute.
Is there a solution to this problem ?
Thanks in advance,
Best regards,
Guy
ALTER procedure [dbo].[SP_CHECK_USERS]
AS
select 'FIDUCIAIRE' as code,moment,nom,machine from MERCATORFIDUCIAIRE.dbo.sessions s1 where (s1.inactif=0) and (s1.nom<>'MercatorIshop')
union select 'TEST1 ' as code,moment,nom,machine from MERCATORTEST1.dbo.sessions s2 where (s2.inactif=0) and (s2.nom<>'MercatorIshop')
union select 'TEST10 ' as code,moment,nom,machine from MERCATORTEST10.dbo.sessions s3 where (s3.inactif=0) and (s3.nom<>'MercatorIshop')
union select 'TEST11 ' as code,moment,nom,machine from MERCATORTEST11.dbo.sessions s4 where (s4.inactif=0) and (s4.nom<>'MercatorIshop')
union select 'TEST12 ' as code,moment,nom,machine from MERCATORTEST12.dbo.sessions s5 where (s5.inactif=0) and (s5.nom<>'MercatorIshop')
union select 'TEST13 ' as code,moment,nom,machine from MERCATORTEST13.dbo.sessions s6 where (s6.inactif=0) and (s6.nom<>'MercatorIshop')
union select 'TEST14 ' as code,moment,nom,machine from MERCATORTEST14.dbo.sessions s7 where (s7.inactif=0) and (s7.nom<>'MercatorIshop')
union select 'TEST15 ' as code,moment,nom,machine from MERCATORTEST15.dbo.sessions s8 where (s8.inactif=0) and (s8.nom<>'MercatorIshop')
union select 'TEST16 ' as code,moment,nom,machine from MERCATORTEST16.dbo.sessions s9 where (s9.inactif=0) and (s9.nom<>'MercatorIshop')
union select 'TEST17 ' as code,moment,nom,machine from MERCATORTEST17.dbo.sessions s10 where (s10.inactif=0) and (s10.nom<>'MercatorIshop')
union select 'TEST18 ' as code,moment,nom,machine from MERCATORTEST18.dbo.sessions s11 where (s11.inactif=0) and (s11.nom<>'MercatorIshop')
union select 'TEST2 ' as code,moment,nom,machine from MERCATORTEST2.dbo.sessions s12 where (s12.inactif=0) and (s12.nom<>'MercatorIshop')
union select 'TEST20 ' as code,moment,nom,machine from MERCATORTEST20.dbo.sessions s13 where (s13.inactif=0) and (s13.nom<>'MercatorIshop')
union select 'TEST21 ' as code,moment,nom,machine from MERCATORTEST21.dbo.sessions s14 where (s14.inactif=0) and (s14.nom<>'MercatorIshop')
union select 'TEST23 ' as code,moment,nom,machine from MERCATORTEST23.dbo.sessions s15 where (s15.inactif=0) and (s15.nom<>'MercatorIshop')
union select 'TEST3 ' as code,moment,nom,machine from MERCATORTEST3.dbo.sessions s16 where (s16.inactif=0) and (s16.nom<>'MercatorIshop')
union select 'TEST4 ' as code,moment,nom,machine from MERCATORTEST4.dbo.sessions s17 where (s17.inactif=0) and (s17.nom<>'MercatorIshop')
union select 'TEST5 ' as code,moment,nom,machine from MERCATORTEST5.dbo.sessions s18 where (s18.inactif=0) and (s18.nom<>'MercatorIshop')
union select 'TEST6 ' as code,moment,nom,machine from MERCATORTEST6.dbo.sessions s19 where (s19.inactif=0) and (s19.nom<>'MercatorIshop')
union select 'TEST7 ' as code,moment,nom,machine from MERCATORTEST7.dbo.sessions s20 where (s20.inactif=0) and (s20.nom<>'MercatorIshop')
union select 'TEST8 ' as code,moment,nom,machine from MERCATORTEST8.dbo.sessions s21 where (s21.inactif=0) and (s21.nom<>'MercatorIshop')
union select 'TEST9 ' as code,moment,nom,machine from MERCATORTEST9.dbo.sessions s22 where (s22.inactif=0) and (s22.nom<>'MercatorIshop')
View 2 Replies
View Related
Oct 12, 2006
Hi everyone,
I am coding under Delphi a software which will be using a SQL Server 2000. I am using ADO components and have the latest MDAC: 2.8 SP1.
The software is using a lot of TADOQuery objects which are connected to the same TADOConnection.
The TADOQuery having for options :
- cursorlocation : useclient
- cursortype : static
- locktype : optimistic
On the first use of a TADOQuery in order to execute any type of request, the processing is fast.
If I am using this same object for others requests, everything is fine and as fast as the first request.
But as soon as I am using another TADOQuery, performances are going down.
In order to give some numbers, some requests can take as much as 17 seconds to be executed. The same request would take at most 100 milliseconds if it were executed with the first TADOQuery.
It is not a problem concerning indexes; if I try to use the request analyzer, all my requests are processed quickly.
I used the SQL Profiler to see the details and saw some interesting things :
the first uses of TADOQuery are of type SQL:BatchCompleted
but switching to another TADOquery make it use another type : RPC:Completed.
Contents of this RPC can be a declaration or an execution of cursor.
More precisely, if I am using only one ADOQuery, I see one line in the Profiler for every block of code opening/reading contents/closing.
But using multiples ADOQuery, I see one declaration of cursor for the opening and then every reading produces one cursorfetch.
So, this declaration of cursor may take a long of time and every fetch too and if there is many lines to send to the client, the network is used for each line to send.
Better, using an TADOQuery to make a request and then simply initializing another one by changing the SQL property and then executing a second time the first TADOQuery makes this last execution used as a cursor serverside.
The problem should then on the side of the client but I can not figure where.
I hope having been as clear as needed !
My wishes is to not have recode the whole database side of our software because it is huge and badly designed so it will be hard to test everything fine.
So before recoding I wish to continue the analysis.
So does anyone knows why is happening this serverside thing ?
And is there a simple way to make it not happen ?
Thanks to everyone for any future help !
View 1 Replies
View Related
