Need suggestion on TSql Query with Joins

Hi All,

Please suggest me is there any performance/other differences between the below two queries.

-query1

select T1.name,T1.Number, T2.Dept, T2.Desig

From T1 Inner Join T2 on T1.EID = T2.EID

-query2

select T1.name,T1.Number, T2.Dept, T2.Desig

From T1 Inner Join (Select Dept, Desig From T2) As T2 on T1.EID = T2.EID

Thanks

Senthil

There is no performance difference for your quires.

Both are executed in same way..

Suppose, if your query2, subquery has any distinct or group by clause then the query1 may perform better than query2.

|||Thanks Sekaran!

Need SQL Performance Advice (Scenario Given)

Hey guys, having a bit of a urgency here. I need to make certain queries faster in our application, lemme give you a scoup. One of the queries i need to optimize or find a better way to do is the current scenario. We have jobs in our system, these jobs have applicants, some of them are direct applicants, some are matches (from searches), and some are applicants with resumes only.
Some of our clients have 100+ jobs, when they see the first page we list all the jobs with 6 counts next to them:

Job Title, Total Applicants, New Applicants, Total Matches, New Matches, Total Resumes, New Resumes

So we have 6 counts for each job, say you have 100+ jobs thats a lot of counts. Currently i'm performing a sub query for each of the counts since they represent different data points, the only thing that changes is the WHERE clause.

As you can see this can be very heavy and in some cases (100+ jobs) depending of number of applicants might take up to a minute to finish.

I would like to get some opinions from you guys on how to make this run faster, obviously caching cannot be considered since they employer must see the live data (counts)

Any help is greatly appreciated.indexes on the join columns?|||yes indexes are in place, i dont really thing that they are the problem,

i think the subqueries are killing it since one query needs to be run to get each of the count right? is there any other way to structure this thing?

thanks for your help|||depends

without seeing the subqueries, it's kinda difficult to tell what's wrong or whether an alternative structure is possible|||Without seeing your code, my first suggestion would be to "unwrap" the subqueries. Make a single pass through the data, doing a single join. Reimplement the counts as sums, and use CASE to provide the "smarts" to make it work.

As Rudy pointed out, without seeing what you are doing, we are pretty sorely limited in how we can help. This is kind of like calling someone on another continent and telling them "my stomach hurts" and asking them what you should do about it.

-PatP

Need some help!

Hi All
I want to blow up the database. Say I have 10 records in a particular table
and I want to increase it to 100 records for performance testing purposes. I
s
there a way to do it in SQL Server?
Thank you
Viccu.Viccu
create table #test (id int not null identity(1,1))
declare @.i int
set @.i=1
while @.i<=100
begin
insert #test default values
set @.i=@.i+1
end
select * from #test
"Viccu" <Viccu@.discussions.microsoft.com> wrote in message
news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
> Hi All
> I want to blow up the database. Say I have 10 records in a particular
> table
> and I want to increase it to 100 records for performance testing purposes.
> Is
> there a way to do it in SQL Server?
> Thank you
> Viccu.|||How about that. In answering your question I grabbed a function that was
posted to this newsgroup by Peter Larson on 5/10/2006 @. 4:13AM (within
microsoft.public.sqlserver.programming there are 12 posts between yours and
his).
Anyway...
--Lets pretend this is your table:
create table #foo (col1 int identity(1,1), col2 varchar(10), col3 datetime)
go
--and here is your 10 rows of data
insert into #foo (col2, col3) values ('test 1', '1/1/2006')
insert into #foo (col2, col3) values ('test 2', '2/1/2006')
insert into #foo (col2, col3) values ('test 3', '1/3/2006')
insert into #foo (col2, col3) values ('test 4', '1/4/2006')
insert into #foo (col2, col3) values ('test 5', '5/1/2006')
insert into #foo (col2, col3) values ('test 6', '1/6/2006')
insert into #foo (col2, col3) values ('test 7', '1/7/2006')
insert into #foo (col2, col3) values ('test 8', '8/1/2006')
insert into #foo (col2, col3) values ('test 9', '1/9/2006')
insert into #foo (col2, col3) values ('test 10', '10/1/2006')
--Peter Larson's function:
CREATE FUNCTION dbo.fnSeqNumbers
(
@.LowLimit INT,
@.HighLimit INT
)
RETURNS @.Values TABLE
(
Value INT
)
AS
BEGIN
DECLARE @.Temp INT
IF @.LowLimit > @.HighLimit
SELECT @.Temp = @.LowLimit,
@.LowLimit = @.HighLimit,
@.HighLimit = @.Temp
INSERT @.Values VALUES (@.LowLimit)
WHILE @.@.ROWCOUNT > 0
INSERT @.Values
SELECT n.Value + t.Items
FROM @.Values n
CROSS JOIN (
SELECT COUNT(*) Items
FROM @.Values
) t
WHERE n.Value + t.Items <= @.HighLimit
RETURN
END
go
-- showing you the results from the function:
--select * from dbo.fnSeqNumbers(1,10)
--this returns 100 rows
select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
cross join dbo.fnSeqNumbers(1,10) b
order by a.col2
--...but you already have 10 in your table so lets only insert 90 rows
insert into #foo (col2, col3)
select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
cross join dbo.fnSeqNumbers(1,9) b
order by a.col2
select @.@.rowcount
--look at your "new" data:
select * From #foo
This solution assumes that you have an identity value as your primary key.
If that is not the case in your table but your primary key is a number you
should be able to convert the number to a string, concatinate the value from
the function, and convert it back to an int that could be used as your
primary key. Hopefully this example gives you a few ideas and points you in
a direction where you can figure out a solution.
Keith Kratochvil
"Viccu" <Viccu@.discussions.microsoft.com> wrote in message
news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
> Hi All
> I want to blow up the database. Say I have 10 records in a particular
> table
> and I want to increase it to 100 records for performance testing purposes.
> Is
> there a way to do it in SQL Server?
> Thank you
> Viccu.|||Thanks much.
"Uri Dimant" wrote:

> Viccu
> create table #test (id int not null identity(1,1))
> declare @.i int
> set @.i=1
> while @.i<=100
> begin
> insert #test default values
> set @.i=@.i+1
> end
> select * from #test
>
> "Viccu" <Viccu@.discussions.microsoft.com> wrote in message
> news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
>
>|||Thank you so much.
"Keith Kratochvil" wrote:

> How about that. In answering your question I grabbed a function that was
> posted to this newsgroup by Peter Larson on 5/10/2006 @. 4:13AM (within
> microsoft.public.sqlserver.programming there are 12 posts between yours an
d
> his).
> Anyway...
> --Lets pretend this is your table:
> create table #foo (col1 int identity(1,1), col2 varchar(10), col3 datetime
)
> go
> --and here is your 10 rows of data
> insert into #foo (col2, col3) values ('test 1', '1/1/2006')
> insert into #foo (col2, col3) values ('test 2', '2/1/2006')
> insert into #foo (col2, col3) values ('test 3', '1/3/2006')
> insert into #foo (col2, col3) values ('test 4', '1/4/2006')
> insert into #foo (col2, col3) values ('test 5', '5/1/2006')
> insert into #foo (col2, col3) values ('test 6', '1/6/2006')
> insert into #foo (col2, col3) values ('test 7', '1/7/2006')
> insert into #foo (col2, col3) values ('test 8', '8/1/2006')
> insert into #foo (col2, col3) values ('test 9', '1/9/2006')
> insert into #foo (col2, col3) values ('test 10', '10/1/2006')
>
> --Peter Larson's function:
> CREATE FUNCTION dbo.fnSeqNumbers
> (
> @.LowLimit INT,
> @.HighLimit INT
> )
> RETURNS @.Values TABLE
> (
> Value INT
> )
> AS
> BEGIN
> DECLARE @.Temp INT
> IF @.LowLimit > @.HighLimit
> SELECT @.Temp = @.LowLimit,
> @.LowLimit = @.HighLimit,
> @.HighLimit = @.Temp
> INSERT @.Values VALUES (@.LowLimit)
> WHILE @.@.ROWCOUNT > 0
> INSERT @.Values
> SELECT n.Value + t.Items
> FROM @.Values n
> CROSS JOIN (
> SELECT COUNT(*) Items
> FROM @.Values
> ) t
> WHERE n.Value + t.Items <= @.HighLimit
> RETURN
> END
> go
> -- showing you the results from the function:
> --select * from dbo.fnSeqNumbers(1,10)
>
> --this returns 100 rows
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,10) b
> order by a.col2
>
> --...but you already have 10 in your table so lets only insert 90 rows
>
> insert into #foo (col2, col3)
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,9) b
> order by a.col2
> select @.@.rowcount
>
> --look at your "new" data:
>
> select * From #foo
>
> This solution assumes that you have an identity value as your primary key.
> If that is not the case in your table but your primary key is a number you
> should be able to convert the number to a string, concatinate the value fr
om
> the function, and convert it back to an int that could be used as your
> primary key. Hopefully this example gives you a few ideas and points you
in
> a direction where you can figure out a solution.
> --
> Keith Kratochvil
>
> "Viccu" <Viccu@.discussions.microsoft.com> wrote in message
> news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
>
>|||google up "generate test data"|||Thank you.Because of the foreign key constraints,say one of my tables has a
value that should
correspond to the value in the other table. How would I include that
particular value
in the query given by you?
insert into #foo (col2, col3)
select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
cross join dbo.fnSeqNumbers(1,9) b
order by a.col2
"Keith Kratochvil" wrote:

> How about that. In answering your question I grabbed a function that was
> posted to this newsgroup by Peter Larson on 5/10/2006 @. 4:13AM (within
> microsoft.public.sqlserver.programming there are 12 posts between yours an
d
> his).
> Anyway...
> --Lets pretend this is your table:
> create table #foo (col1 int identity(1,1), col2 varchar(10), col3 datetime
)
> go
> --and here is your 10 rows of data
> insert into #foo (col2, col3) values ('test 1', '1/1/2006')
> insert into #foo (col2, col3) values ('test 2', '2/1/2006')
> insert into #foo (col2, col3) values ('test 3', '1/3/2006')
> insert into #foo (col2, col3) values ('test 4', '1/4/2006')
> insert into #foo (col2, col3) values ('test 5', '5/1/2006')
> insert into #foo (col2, col3) values ('test 6', '1/6/2006')
> insert into #foo (col2, col3) values ('test 7', '1/7/2006')
> insert into #foo (col2, col3) values ('test 8', '8/1/2006')
> insert into #foo (col2, col3) values ('test 9', '1/9/2006')
> insert into #foo (col2, col3) values ('test 10', '10/1/2006')
>
> --Peter Larson's function:
> CREATE FUNCTION dbo.fnSeqNumbers
> (
> @.LowLimit INT,
> @.HighLimit INT
> )
> RETURNS @.Values TABLE
> (
> Value INT
> )
> AS
> BEGIN
> DECLARE @.Temp INT
> IF @.LowLimit > @.HighLimit
> SELECT @.Temp = @.LowLimit,
> @.LowLimit = @.HighLimit,
> @.HighLimit = @.Temp
> INSERT @.Values VALUES (@.LowLimit)
> WHILE @.@.ROWCOUNT > 0
> INSERT @.Values
> SELECT n.Value + t.Items
> FROM @.Values n
> CROSS JOIN (
> SELECT COUNT(*) Items
> FROM @.Values
> ) t
> WHERE n.Value + t.Items <= @.HighLimit
> RETURN
> END
> go
> -- showing you the results from the function:
> --select * from dbo.fnSeqNumbers(1,10)
>
> --this returns 100 rows
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,10) b
> order by a.col2
>
> --...but you already have 10 in your table so lets only insert 90 rows
>
> insert into #foo (col2, col3)
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,9) b
> order by a.col2
> select @.@.rowcount
>
> --look at your "new" data:
>
> select * From #foo
>
> This solution assumes that you have an identity value as your primary key.
> If that is not the case in your table but your primary key is a number you
> should be able to convert the number to a string, concatinate the value fr
om
> the function, and convert it back to an int that could be used as your
> primary key. Hopefully this example gives you a few ideas and points you
in
> a direction where you can figure out a solution.
> --
> Keith Kratochvil
>
> "Viccu" <Viccu@.discussions.microsoft.com> wrote in message
> news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
>
>|||You could hardcode a single value within the select, modify the function to
return an additional column that has "random" FK values passed out of it or
you could just use (re-use) the foreign key data that you already have in
your table (#foo in my example). You probably want to load all the columns
when you insert data...just make sure that you query your FK column from
your base table when joining to the function.
Keith Kratochvil
"Viccu" <Viccu@.discussions.microsoft.com> wrote in message
news:D11F6AF9-11D2-43FB-AF0D-40726220E012@.microsoft.com...
> Thank you.Because of the foreign key constraints,say one of my tables has
> a
> value that should
> correspond to the value in the other table. How would I include that
> particular value
> in the query given by you?
> insert into #foo (col2, col3)
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,9) b
> order by a.col2sql

Need Performance Tips - Zip Code Locator

I have a stored

procedure used to lookup ad's similar to ebay. We want to allow the customer to

search based on their zip code in relation to the items location. This query

works but it takes too long. We only want to return the top 100 records. It also

uses indexed searching for the main search terms. Any ideas what we can do to

improve performance?

CREATE PROCEDURE Search
@.SearchText

varchar(200),
@.CategoryID int = Null,
@.TxtDesc bit = 0,
@.PriceMin money

= 0,
@.PriceMax money = 250000,
@.ZipCode varchar(5) = Null,
@.Distance

smallint = 0,
@.ManMul int = 1000,
@.ModMul int = 1000,
@.TitMul int =

100,
@.DesMul int = 1
AS
BEGIN
DECLARE @.CenterLat float
DECLARE

@.CenterLon float

-- Earth Radius In Miles
DECLARE @.EarthRadius

float
SET @.EarthRadius = 3958.76

-- Determine Lat/Lon For User's Zip

Code
SELECT @.CenterLat = Lat,
@.CenterLon = Long
FROM

List_ZipCodes
WHERE Zip_Code = @.ZipCode

DECLARE @.CntXAxis

float
DECLARE @.CntYAxis float
DECLARE @.CntZAxis float

SET @.CntXAxis

= cos(radians(@.CenterLat)) * cos(radians(@.CenterLon))
SET @.CntYAxis =

cos(radians(@.CenterLat)) * sin(radians(@.CenterLon))
SET @.CntZAxis =

sin(radians(@.CenterLat))

SELECT

TOP(100)
C.Classified_ID,
Manufacturer,
Model,
Title
FROM

Classifieds C
INNER JOIN Classifieds_Categories ON C.Classified_ID =

Classifieds_Categories.Classified_ID
LEFT OUTER JOIN

FREETEXTTABLE(Classifieds, (Manufacturer), @.SearchText)AS f1 ON C.Classified_ID

= f1.[Key]
LEFT OUTER JOIN FREETEXTTABLE(Classifieds, (Model), @.SearchText)AS

f2 ON C.Classified_ID = f2.[Key]
LEFT OUTER JOIN FREETEXTTABLE(Classifieds,

(Title), @.SearchText)AS f3 ON C.Classified_ID = f3.[Key]
INNER JOIN

List_ZipCodes AS ZC ON ZC.Zip_Code = C.Shipping_FromZip
WHERE

((COALESCE(f1.[Rank], 0)* @.ManMul) + (COALESCE(f2.[Rank], 0)* @.ModMul) +

(COALESCE(f3.[Rank], 0) * @.TitMul)) > 500
AND Active = 1
AND

Price_Asking >= @.PriceMin
AND Price_Asking <= @.PriceMax
AND

(Category_ID = COALESCE(@.CategoryID, Category_ID) OR Category_ID IN (SELECT

Category_ID FROM List_Categories WHERE Parent_Category_ID = @.CategoryID))
AND

Shipping_FromZip Is Not Null
AND (@.EarthRadius * acos((cos(radians(ZC.Lat))

* cos(radians(ZC.Long)))*@.CntXAxis + (cos(radians(ZC.Lat)) *

sin(radians(ZC.Long)))*@.CntYAxis + (sin(radians(ZC.Lat)))*@.CntZAxis)) <=

@.Distance
ORDER BY ((COALESCE(f1.[Rank], 0)* @.ManMul) + (COALESCE(f2.[Rank],

0)* @.ModMul) + (COALESCE(f3.[Rank], 0) * @.TitMul)) DESC
END

The ultimate cost for this query is coming from the FTS query. Basically, sqlserver will have to wait for the result to come back for each freetexttable() before it can join to the base table. And also, the resultset returned from the freetexttable() is not indexed. So, if there are lots of data in the resultset, the main query can take a long time to join. In case you have lots of data return from FTS, you might want to create a temp/working table to hold the data. You can then index them before joining with the base table.

NEED HELP! SQL Merge Replication

I have a merge replication process that is having some major performance issues. The replication process seems to run just fine for about a half hour (200k records), and then it hits a brick wall. I continually get the message:
"The process is running and is waiting for a response from one of the backend connections"
Once I get this message, the process will then go through 4 more batch downloads (2000 records per batch), and then give me that message 2 or 3 more times and then continue to download. This appears to only happen on a specific table. I have exhausted m
y thoughts on this and would really appreciate ANY help.
The subscriber is running MSDE 2000 on a pentium 3 1.7GHtz machine with 1GB of RAM.
Thank you,
Thanks!
Tony D
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Set QueryTimeout to a larger value. You might want to post replication
related questions to microsoft.public.sqlserver.replication
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tony DiGiorgio" <tony.digiorgio@.immedient.com> wrote in message
news:Okua7om$EHA.2568@.TK2MSFTNGP11.phx.gbl...
>I have a merge replication process that is having some major performance
>issues. The replication process seems to run just fine for about a half
>hour (200k records), and then it hits a brick wall. I continually get the
>message:
> "The process is running and is waiting for a response from one of the
> backend connections"
> Once I get this message, the process will then go through 4 more batch
> downloads (2000 records per batch), and then give me that message 2 or 3
> more times and then continue to download. This appears to only happen on
> a specific table. I have exhausted my thoughts on this and would really
> appreciate ANY help.
> The subscriber is running MSDE 2000 on a pentium 3 1.7GHtz machine with
> 1GB of RAM.
> Thank you,
> Thanks!
> Tony D
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
> ASP.NET resources...