How To Tell If One Query Is Faster Than Another

Jul 26, 2006

I have rewritten a stored procedure that consists of a single select that selects from a view. Essentially I combined the select in the view and the select in the sp into one select. I am now trying to determine if the new version is faster.

The estimated execution plan gives a ratio of 96% : 4% in favour of the new version when I run them together from a query window but when I try to time them I can't get a satisfactory result.

If I run each query once and display the difference between start and end time, they display 0. If I run each one 100, 200, etc times I get different results each time.

Is there anything I can do?

View 5 Replies


ADVERTISEMENT

I Need A Faster Query

Jun 20, 2007

Hi y'all,
I've recently run a profiler on my code and following query took 7 seconds:
SELECT TOP 10 UI, COUNT(UI) AS Expr1 FROM table WHERE (UI <> 'custom_welcome') GROUP BY UI ORDER BY COUNT(UI) DESC
Is it possible to rewrite this so my code gets faster? It's also possible that it's due to the size of the table?
Thanks in advance! I'll let you know how long your query takes :)

View 4 Replies View Related

Faster SQL Query

Mar 31, 2004

Hi!
I M basically an application developer & use simple sql queries in my programmings. I do not have much idea abt tuning/auditing part & thatswhy i m unable to answer them properly in my interviews. Can anybody give me some tips?????

Question 1:
In a stored procedure, One SELECT stmt is there & depending upon the @rowcount, it updates around 14000 records which is also written inside this stored procedure. Instead of writing this way, there is some other way which is faster than this. Can anybody tell me the correct way???

Question 2:Can anybody give me few examples like this?????? I need them desparetly.

Thanx. Bye.

View 37 Replies View Related

Faster Query Results

Oct 17, 2007

Can anyone give me the basics of speeding up reports that use queries or views or nested views?
Current reports are now taking over 2 minutes to show.We have thousands and sometimes even millions of records to report against.Queries have 4 and 5 table joins etc.
We are using ASP.NET 2.0 in Visual Studio 2005 and Crystal Reports.
Thanks
 

View 4 Replies View Related

Help Make Query Run Better (faster)

Mar 20, 2007

I need help makeing the following query run more efficently.


Code:


SELECT t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip
FROM Landlord_tbl t1
left outer join Mail_tbl t2
ON t2.potentialSitesID = t1.potentialSitesID
WHERE t2.mailed_out_date is null and NOT(t1.firstName+t1.lastName) is Null
GROUP BY t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip
ORDER BY t1.firstName, t1.lastName, t1.city, t1.state

View 3 Replies View Related

Query Run Time (which Should Be Faster?)

Feb 28, 2008

Hi, when I execute the sql in DBTestArchive and then DBTest query analyzer , I found that the run time is shorter in DBTest. Can I make assumption that the query run time is shorter in DBTest if I select data from it and insert into another database? Thanks.



INSERT INTO DBTestArchive.dbo.tblVendorMasterArchive
SELECT * FROM DBTest.dbo.tblVendorMaster

View 1 Replies View Related

Better (faster) Way Of Handling Query

Mar 26, 2008



I have a query which will display the employee name who is handling maximum number of project.
the query is



Code Snippet

select EmployeeDetails.Empid,

EmployeeDetails.FirstName + ' '+EmployeeDetails.LastName as EmpName,

count(*) as Number_Of_Projects
from EmployeeDetails left outer join LUP_EmpProject

on EmployeeDetails.Empid= LUP_EmpProject.Empid
group by EmployeeDetails.Empid,EmployeeDetails.FirstName,EmployeeDetails.LastName
HAVING count(*)=(SELECT TOP 1 COUNT(EmpID)



FROM LUP_EmpProject GROUP BY EmpID ORDER BY 1 DESC)






where EmployeeDetails table contain empname and empid. LUP_EmpProject table contain empid and project id.

Can anybody help me in writing a better query than the above given?

Please help!!!!!!!!!!

View 4 Replies View Related

Drop PK And Query Runs Faster. Why?

Aug 23, 2000

I have a query which joins multiple tables. This query has suddenly begun to take up to 2 minutes to run (vs. 5-10 seconds previously).

No major change in number of records in the tables (currently about 220,000). When I remove the PK from one of the tables which then forces a tablescan, the query returns to running in 5-10 seconds. If I add the PK back, performance is back to 2 minutes.

Any suggestions short and/or long term?

Thanks for any help.

View 1 Replies View Related

Any Clue To Make This Query Run Faster

Oct 3, 2006

Any help would be really appreciated....
My stored procedure...




CREATE PROCEDURE business3rd7
@Fromdate DATETIME,
@ToDate DATETIME
AS



select distinct CONVERT(VARCHAR(10),Receipts.Companynumber1)+CONVE RT(VARCHAR(10),Receipts.Companynumber2) as co ,
Receipts.Premium1+Receipts.Premium2 as Premium,
"CAN"=case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,
"NET"=Receipts.Premium1+Receipts.Premium2-case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,

"#NEW"=case when Receipts.transactiontype='NEW' then count(Receipts.policynumber) else
0 end,

-------
"$NEW"=
case when Receipts.transactiontype='NEW' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#REN"=case when Receipts.transactiontype='REN' then count(Receipts.policynumber) else
0 end,
"$RENEW"=
case when Receipts.transactiontype='REN' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#AP"=case when Receipts.transactiontype='AP' then count(Receipts.policynumber) else 0
end,
"$AP"=
case when Receipts.transactiontype='AP' then (Receipts.Premium1+Receipts.premium2) else
0 end,
"#SENT"=case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) end,

"%"=case when case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end =0 then 0
when case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) else 0 end=0 then 0
else
case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end /case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) else 0 end end,


"Current Year"= case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(clubamount) end,
"Previous Year"=case when policy.Renewalofferdate between DateAdd(year, -1, @Fromdate)
AND DateAdd(year, -1, @ToDate) then count(clubamount) end,
"#AA"=count(receipts.clubamount),
"$AA"=sum(receipts.clubamount)

FROM Receipts,Policy
where Receipts.Agencyid=Policy.Agentid
group by
Receipts.CompanyNumber1,Receipts.CompanyNumber2,
Receipts.Premium1,Receipts.Premium2,
Receipts.TransactionType,policy.Renewalofferdate,
Receipts.Agencyid



GO


Query plan...

----------------------Query Plan

|--Sort(DISTINCT ORDER BY:([Expr1008] ASC, [Expr1009] ASC, [Expr1010] ASC, [Expr1011] ASC, [Expr1012] ASC, [Expr1013] ASC, [Expr1014] ASC, [Expr1015] ASC, [Expr1016] ASC, [Expr1017] ASC, [Expr1018] ASC, [Expr1019] ASC, [Expr1020] ASC, [Expr1021] ASC, [Ex
|--Compute Scalar(DEFINE:([Expr1008]=Convert([Receipts].[CompanyNumber1])+Convert([Receipts].[CompanyNumber2]), [Expr1009]=[Receipts].[Premium1]+[Receipts].[Premium2], [Expr1010]=If ([Receipts].[TransactionType]='CAN') then ([Receipts].[Premium1]+[R
|--Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1076]), [Expr1005]=Convert([Expr1077]), [Expr1006]=Convert([Expr1078]), [Expr1007]=If ([Expr1078]=0) then NULL else [Expr1079]))
|--Stream Aggregate(GROUP BY:([Receipts].[CompanyNumber1], [Receipts].[CompanyNumber2], [Receipts].[Premium1], [Receipts].[Premium2], [Receipts].[TransactionType], [Policy].[RenewalOfferDate], [Receipts].[AgencyID]) DEFINE:([Expr1076]=COUN
|--Sort(ORDER BY:([Receipts].[CompanyNumber1] ASC, [Receipts].[CompanyNumber2] ASC, [Receipts].[Premium1] ASC, [Receipts].[Premium2] ASC, [Receipts].[TransactionType] ASC, [Policy].[RenewalOfferDate] ASC, [Receipts].[AgencyID] ASC))
|--Hash Match(Inner Join, HASH:([Policy].[AgentID])=([Receipts].[AgencyID]), RESIDUAL:([Policy].[AgentID]=[Receipts].[AgencyID]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Policy]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Receipts]))



The two tables has number of records as 13349 and 97032.It taking more than 30 mins...
Any way to make it faster...

View 14 Replies View Related

AdHoc Query Faster Than Stored Proc?

Aug 28, 2004

Yesterday i face a strange SQL Server 2000 behaviour :-(

I had a query that was wrapped inside a stored procedure, as usual.
Suddenly, the stored procedure execution time raised from 9 secs to 80.

So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs.
Tried stored procedure again, and speed again set to 80 secs.

Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.

My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?

View 5 Replies View Related

Why Is DTS Faster Than A Query To Load A Remote Table?

Apr 23, 2007

Hello

DTS is notoriously faster than making the following statement:

insert into synonym_MyRemoteTable
select * from myLocalTable

Why is it so?

Thanks a lot.

View 6 Replies View Related

Faster Query - Select By One Or Multiple Fields

Feb 1, 2008

Let's say I have a table of users.
Let's imagine there's two fields:
username (PK),
password

Now I need to authenticate a user against this table. What is the recommended approach? Is it better / faster to
(1) SELECT * FROM [User]
WHERE username = 'whatever' AND password='whatever'
or
(2) SELECT * FROM [User]
WHERE username = 'whatever'
and then in my code check that the record returned matched the password?

View 6 Replies View Related

Why Does A Query Runs Considerably Faster Than Stored Procedure?

Dec 29, 2003

I have an stored procedure that takes 23 seconds to execute. However, the same query is executed in 4 seconds.

All the tests where made using the Query Analizer.

What could be wrong?

Thanks a lot in advance.

View 13 Replies View Related

How Do I Make 30 Sec Running Query (select C1 Sum(x) From T1 Where C1 &&> 1000 Group By C1) Run Faster?

Aug 10, 2007

It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.


Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?

if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?


Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?


Thanks in for you help in advance

View 1 Replies View Related

Same Statement Executes 10 Times Faster As Raw Sql In Query Analyzer Then In A Stored Proc

Aug 15, 2007



Hi,


I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this.


We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index.


Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement?


Thank you,


Michael Tzoanos

View 4 Replies View Related

Query Executes Faster In Grid Mode Than In Text Mode!!

Mar 9, 2000

Hello,everyone!!

There is a query which when executed in the grid mode(ctrl+d) takes approx 0.02 seconds(about 21,000
rows) But when I execute in the text mode, it takes about 0.40 seconds!!
Why is this difference?
Also, when the records from this table are read from a VB application, they are equally slow (as in the text mode!)
Why is it so slow on the text mode & relatively faster in the grid mode?
Has anyone got any idea on ‘Firehose’ style cursor ?(which may speed up access of data in the VB application)

Rgds,
Adie

View 1 Replies View Related

Which Would Be Faster ?

Mar 28, 2007

hi all,    if i have a comma delimited string and want to insert each delimited substring into a table which of the following way is faster?pass the whole string into the a stored procedure and loop through the delimited string and pick out the substring and insert into the table orloop and pass the substring into a stored procedure and insert N times?or any other better ways someone could suggest me to do thanks! 

View 6 Replies View Related

Which Is Faster LIKE Or IN

Jul 27, 2001

i have a query that i can use either and get the same results. i just need to shave some time off... which is faster the LIKE or IN () ???

thanks

kim

View 2 Replies View Related

Can This Be Done Faster?

Jan 16, 2006

I was just wondering if this can be done any faster? code-wise that is...

Don't mind the converts, can't do without them, as the data discipline for the source table isn't always reliable, while I have to be absolutely sure the destination data ends in the required format.



UPDATE MATCH_basistabel
SET MATCH_basistabel.matchfelt = convert(varchar(50),ALL_tbl_medlemsinfo.søgenavn),
MATCH_basistabel.søgenavn = convert(varchar(50),ALL_tbl_medlemsinfo.søgenavn),
MATCH_basistabel.medlemsnavn = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnav n),
MATCH_basistabel.medlemsnavn2 = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnav n2),
MATCH_basistabel.medlemsnummer = ALL_tbl_medlemsinfo.medlemsnummer,
MATCH_basistabel.nationalitet = convert(varchar(10), ALL_tbl_medlemsinfo.nationalitet),
MATCH_basistabel.organisationsnummer = convert(varchar(10),ALL_tbl_medlemsinfo.organisati onsnummer),
MATCH_basistabel.medlemskab = convert(varchar(20), ALL_tbl_medlemsinfo.medlemskab),
MATCH_basistabel.ipdn = ALL_tbl_medlemsinfo.ipdn,
MATCH_basistabel.ipdnroll = convert(varchar(20), ALL_tbl_medlemsinfo.ipdroll),
MATCH_basistabel.franavision = 1
FROM MATCH_basistabel, ALL_tbl_medlemsinfo
WHERE isnumeric(matchfelt) = 1
AND (convert(int, MATCH_basistabel.matchfelt) = convert(int, ALL_tbl_medlemsinfo.medlemsnummer)
AND MATCH_basistabel.franavision = 0)

View 14 Replies View Related

Run Faster

Feb 17, 2006

Hi Guys,

I have SQL file but it run slowly when comes to huge record. How do I make it faster. I do create an index but how to make use the index? Pls help me on this...

Many Thanks,

Regards,
Shaffiq

View 6 Replies View Related

NEED TO GO FASTER

Jun 18, 2007

View 2 Replies View Related

Which Is Faster?

Jul 20, 2005

I'm sonewhat new to MS SQL Server and I'm wondering about which of thefollowing two queries would be faster:DECLARE @ResidencesBuilt intDECLARE @BarracksBuilt intDECLARE @AirBaysBuilt intDECLARE @NuclearPlantsBuilt intDECLARE @FusionPlantsBuilt intDECLARE @StarMinesBuilt intDECLARE @TrainingCampsBuilt intDECLARE @FactoriesBuilt intSELECT@ResidencesBuilt = SUM(CASE WHEN BuildingType = 0 THEN Built END),@BarracksBuilt = SUM(CASE WHEN BuildingType = 1 THEN Built END),@AirBaysBuilt = SUM(CASE WHEN BuildingType = 2 THEN Built END),@NuclearPlantsBuilt = SUM(CASE WHEN BuildingType = 3 THEN Built END),@FusionPlantsBuilt = SUM(CASE WHEN BuildingType = 4 THEN Built END),@StarMinesBuilt = SUM(CASE WHEN BuildingType = 5 THEN Built END),@TrainingCampsBuilt = SUM(CASE WHEN BuildingType = 6 THEN Built END),@FactoriesBuilt = SUM(CASE WHEN BuildingType = 7 THEN Built END)FROM BuildingsGROUP BY kdIDHAVING kdID = 2902Or:DECLARE @ResidencesBuilt intDECLARE @BarracksBuilt intDECLARE @AirBaysBuilt intDECLARE @NuclearPlantsBuilt intDECLARE @FusionPlantsBuilt intDECLARE @StarMinesBuilt intDECLARE @TrainingCampsBuilt intDECLARE @FactoriesBuilt intSET @ResidencesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 0AND kdID = 2902)SET @BarracksBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 1 ANDkdID = 2902)SET @AirBaysBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 2 ANDkdID = 2902)SET @NuclearPlantsBuilt = (SELECT Built FROM Buildings WHERE BuildingType =3 AND kdID = 2902)SET @FusionPlantsBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 4AND kdID = 2902)SET @StarMinesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 5AND kdID = 2902)SET @TrainingCampsBuilt = (SELECT Built FROM Buildings WHERE BuildingType =6 AND kdID = 2902)SET @FactoriesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 7AND kdID = 2902)The data source is:kdID BuildingType Built2902 6 02902 7 02902 4 02902 0 802902 2 02902 1 52902 3 402902 5 10Or:CREATE TABLE [dbo].[Buildings] ([kdID] [int],[BuildingType] [tinyint],[Built] [int])INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 0, 80)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 1, 5)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 2, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 3, 40)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 4, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 5, 10)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 6, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 7, 0)Analyzer says the first would be faster, but it has a lot of SUM()'s andwhatnot so I'm not too sure about this. There are also about 1000 rows inthe actual Buildings table. This will be a part of a stored procedure.

View 3 Replies View Related

Faster Way To Do This?

May 7, 2007

I want to know the # of users on our web site for each month in a given year. I'm looking for a faster way to do this--perhaps one that can leverage an index instead of reading the entire table! (My avg disk queue right now is above 7 and the query takes about 90 seconds).



Here's my current SP. Basically I'm calculating each month/year and using UNION to join them together, then pivot to rotate.



USE [TNS]

GO

/****** Object: StoredProcedure [dbo].[Unique_Login_IPs] Script Date: 05/07/2007 12:38:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[Unique_Login_IPs]

(

@year1 int,

@year2 int

)

AS

BEGIN

SET NOCOUNT OFF;



-- Define the years for testing purposes

set @year1 = 2006

set @year2 = 2007



SELECT month,[2006] as y2006,[2007] as y2007

FROM

(

SELECT @year1 AS year, 1 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 1)) as tmpy1_1

UNION

SELECT @year1 AS year, 2 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 2)) as tmpy1_2

UNION

SELECT @year1 AS year, 3 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 3)) as tmpy1_3

UNION

SELECT @year1 AS year, 4 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 4)) as tmpy1_4

UNION

SELECT @year1 AS year, 5 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 5)) as tmpy1_5

UNION

SELECT @year1 AS year, 6 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 6)) as tmpy1_6

UNION

SELECT @year1 AS year, 7 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 7)) as tmpy1_7

UNION

SELECT @year1 AS year, 8 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 8)) as tmpy1_8

UNION

SELECT @year1 AS year, 9 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 9)) as tmpy1_9

UNION

SELECT @year1 AS year, 10 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 10)) as tmpy1_10

UNION

SELECT @year1 AS year, 11 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 11)) as tmpy1_11

UNION

SELECT @year1 AS year, 12 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 12)) as tmpy1_12

UNION

SELECT @year2 AS year, 1 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 1)) as tmpy1_1

UNION

SELECT @year2 AS year, 2 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 2)) as tmpy2_2

UNION

SELECT @year2 AS year, 3 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 3)) as tmpy2_3

UNION

SELECT @year2 AS year, 4 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 4)) as tmpy2_4

UNION

SELECT @year2 AS year, 5 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 5)) as tmpy2_5

UNION

SELECT @year2 AS year, 6 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 6)) as tmpy2_6

UNION

SELECT @year2 AS year, 7 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 7)) as tmpy2_7

UNION

SELECT @year2 AS year, 8 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 8)) as tmpy2_8

UNION

SELECT @year2 AS year, 9 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 9)) as tmpy2_9

UNION

SELECT @year2 AS year, 10 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 10)) as tmpy2_10

UNION

SELECT @year2 AS year, 11 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 11)) as tmpy2_11

UNION

SELECT @year2 AS year, 12 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 12)) as tmpy2_12

) piv

PIVOT

(

SUM(cnt)

FOR year IN

([2006],[2007])

) as child

END

View 1 Replies View Related

Sqldatasource Faster Than DAL???

May 14, 2007

Hi all, I m new to this forum and this is my first question. I m having 2 pages in my web site ... page 1 query directly to db using sqldatasource, the second page query through a BLL then DAL by following the step in this tutorial (http://www.asp.net/learn/dataaccess/tutorial02vb.aspx?tabid=63)....  Page 1 is using a "Like" query search and the Page 2 is the normal displaying some product detail.... Under normal circumstances, one will expect Page 1 will be way fastest than the Page 2... however the problem is Page 1 is in thunder speed while Page 2 takes 10 secs to load... 10 seconds is really not acceptable... I really couldnt figure out what happens... both Page 1 and Page 2 are using the same connection string which connection through a DSN....  How is the connection different by using sqldatasource and DAL?? Could someone please help....  ThanksP.S. I m using a Pervasive database 

View 5 Replies View Related

Faster Than Cursor?

Apr 8, 2008

I have a cursor prcedure that is pretty slow because as the cursor moves through the data I have three select statement on the same table to find other rows information. Is there a better way to do this?
 Simple Example of Code is:
DECLARE MyVARABLES 1 to X
DECLARE c1 CURSOR
FOR
SELECT MyData1, MyData2 to X
FROM MyTable
FOR UPDATE OF MyUpdateData
--Start Cursor
OPEN c1
FETCH NEXT FROM c1
INTO MyVariables
--LOOP
WHILE @@FETCH_STATUS = 0
BEGIN
-----------------------
-- Get other rows data to add to this rows data ......GUESSING THIS IS THE SLOW PART as the table is LARGESELECT MyVar1 = MyData1
FROM MyTable
WHERE MyTableColumns = MyVariables AND MyTableColumns2 <> MyVariables2  --FINDS OTHER ROW (I have three of these)   

--Calculate & Update
If MyVarable = 'this or that'
BEGIN
UPDATE MyTable
SET MyUpdateData = MyVar1 * x *y WHERE CURRENT OF c1
END
-------------------
-- NextFETCH NEXT FROM c1
INTO MyVarables 1 to xEND
CLOSE c1
DEALLOCATE c1

View 2 Replies View Related

Transactions A Little Bit Faster

May 18, 2004

I´ve created a class to make some standard transaction development a little bit faster. The destructor seem to run, but something makes this object slow down the database, if SqlTransaction and/or SqlConnection isnt manualy handled with the method Commit(). Any ideas on how to handle the SqlTransaction and SqlConnection better?

public class DataTransaction
{
private bool blnError = false;
private ArrayList arrErrorList = new ArrayList();
private SqlConnectionobjConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString());
private SqlTransactionobjTransaction;

public DataTransaction()
{
objConnection.Open();
objTransaction = objConnection.BeginTransaction();
}
~DataTransaction()
{
if(objTransaction.Connection != null)
{
objTransaction = null;
objConnection.Close();
}
}

public int ExecuteNonQuery(string Query)
{
int intRowsAffected = -1;

SqlCommand objCommand = new SqlCommand(Query, objConnection, objTransaction);

try
{
intRowsAffected = objCommand.ExecuteNonQuery();
}
catch(Exception e)
{
blnError = true;
arrErrorList.Add(e.ToString());
}
finally
{
objCommand.Dispose();
}
return intRowsAffected;
}

public System.Object ExecuteScalar(string Query)
{
System.Object objToReturn = null;

SqlCommand objCommand = new SqlCommand(Query, objConnection, objTransaction);

try
{
objToReturn = objCommand.ExecuteScalar();
}
catch(Exception e)
{
blnError = true;
arrErrorList.Add(e.ToString());
}
finally
{
objCommand.Dispose();
}
return objToReturn;
}
public bool Commit()
{
if(!blnError)
{
objTransaction.Commit();
return true;
}
else
{
objTransaction.Rollback();
return false;
}
objConnection.Close();
}
}

View 2 Replies View Related

Does A Job Execute MUCH Faster???

Dec 20, 2004

Hi,

Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.

However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLE job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs. And I am talking about major improvement in terms of completion of the tasks here :)

Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.

I'll appreciate comments. Thanks

View 7 Replies View Related

Which WHERE Clause Would Be Faster?

Jun 17, 2004

Hypothetically,

If I had a WHERE clause that had to compare a string to another string would it be faster one way or another if I broke it down to three different, smaller searches?

An example:


WHERE a = 'abc'
OR a = 'def'
OR a = 'ghi'

as opposed to:

WHERE a = 'abcdefghi'



ddave

View 2 Replies View Related

Does A Job Execute MUCH Faster???

Dec 20, 2004

Hi,

Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.

However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLe job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs.

Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.

I'll appreciate comments. Thanks

View 2 Replies View Related

Which DELETE Will Be Faster?

Feb 23, 2006

A little background on my database structure:

I have a live database and an archive database. I update the archive tables once a day from the live tables using:

INSERT INTO arc_table
SELECT * FROM cur_table AS cur
WHERE NOT EXISTS
(SELECT * FROM arc_table AS arc
WHERE arc.key = cur.key)

GO


This inserts newer records into the archive tables from the live tables.


I have two different methods to clean the live tables once a week but keep data from the previous week. Both methods have been verified to delete the same rows.


DELETE cur_table
WHERE EXISTS
(SELECT key FROM arc_table AS arc
WHERE arc.key = cur_table.key)
AND date_time < GetDate() - 7

GO


Second method modified from BOL - deletes identical rows


DELETE cur_table
FROM (SELECT key FROM arc_table) AS arc
WHERE arc.key = cur_table.key
AND date_time < GetDate() - 7

GO


I read that "WHERE [NOT] EXISTS" is faster than "WHERE [NOT] IN" but this is the first time I have seen DELETE xx FROM (SELECT ----)

I'd like to know which procedure will be faster and/or better.

View 1 Replies View Related

Select Into Really Faster

Sep 26, 2006

HelloI need this really faster in mS SQL 2000Usernumber (int)reportid (FK)reportreportid (PK)Category (int)SELECT A, B, C, D INTO UserCopy FROM UserWHERE User.reportid IN (SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Category) AND user.number NOT IN (120,144,206,345,221,789,548,666,1204,4875,22,135, 777,444)can return a more than 1000 rows (an the table = 10.000 rows): SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Categoryand the table user has a few millions rowsReport.ReportId is a Primary key for User.reportid (FK) for the moment it takes up to 3 minutes, i need to do that in 30 seconds maximumthank you for helping

View 14 Replies View Related

How Can I Make It Faster?

Apr 17, 2008

I recently installed SQL2005 Express on a Dell Precision workstation to be accessed by 6 users. The PC has XP Pro, 2GB memory, and 2.0GHz core2due CPU. This PC is dedicated for the database only.
I also set XP Performance options in System Properties to Background services and System cache.
Now, the users are experiencing slow response from the database.
How can I make the database response faster?
Thanks for your input.

View 4 Replies View Related

What Is Faster: Dynamic Sql WITH Or WITHOUT Parameters?

Mar 19, 2004

Here is he issue.

* We have a DAL that generates all SQL dynamically out of a nobject model. Standard very powerfull O/R mapper.
* In the DAL, for CRUD operations, we generate the statements dynamically. As an example, let's take INSERT.
* The insert is generated ONCE, with parameters, and cached. For every reuse, the parameters are replaced (in value), and the whole thing commited.

I see hte following negative: I can not easily batch multiple inserts. Parameters have to be unique per batch. So, if I want to batch two inserts, I need two sets of parameters.

Alternative:

Instead of generating the SQL with parameters, we generate the SQL as a string ready to be inserted for / with a String.Format, and then I encode the parameters and make one SQL String out of this. Now, please - don't say "sql injection", we are not that stupid, the layer handles this already, properly encoding all dangerous values.

With this approach, the SQL statement would be a string and not use any parameter. As a result, I could batch them up as much as I want (ok, up to a certain string size). I need to keep parameters around anyway (for blobs etc.), but most objects do not have blobs, and the SQL is prettty small. This small SQL could be batched significantly (100 statements per batch, propably mode) and be submitted to the database. As a result, the round trips to the databae would go down.

Now, my question is - which of the two approaches is more advisable, from a performance point of view? Again, stuff like SQL injection and ease of handling are totally irrelevant - the SQL never leaves the DAL and is generated in there, and we will go through a lot of complexitiy for higher performance.

Normally I would say batching should be better. SQL Server can auto-parameterize the statements (reusing the query plan), and / but the network round trips are the larger issue here.

Any comments on this?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved