Linked Server Query

20 Oct 2005 9:04 PM

vibs

When I execute the SQL Statement in the SQL Query Analyzer, I get the results
returned in 4 seconds but when I use the same query from a stored procedure
and execute it from SQL Analyzer, it is taking too long about 50-55 seconds.
Am I missing something? I have a join between external and internal table.

Thanks!!
Vibs

SELECT
    AAPSUser.[ID]
    , AAPSUser.First_Name
    , AAPSUser.Last_Name
    , AAPSUser.Second_Sections,
    [UserExtraInfo].[title],
    [UserExtraInfo].[profile1_text],
    [UserExtraInfo].[profile2_text],
    [UserExtraInfo].[profile3_text]
    FROM
    AAPS.AAPS_Web.DBO.iMIS_Data as AAPSUser
INNER JOIN UserExtraInfo ON AAPSUser.[ID] = UserExtraInfo.Id
WHERE
    AAPSUser.[ID] = 003479

20 Oct 2005 9:10 PM

Jerry Spivey

Are you running the query and the proc containg the query locally i.e, same
SQL Server? (Same security creditials as well?) I.e, log into QA and run
the query then directly below (same window) exectue the proc (same query in
proc right?). You can also compare the execution plans as well for any
visable differences.

HTH

Jerry
Show quote

"vibs" <v***@discussions.microsoft.com> wrote in message
news:6DC8F102-2C0F-4CCE-8831-1F228BAF81AC@microsoft.com...
> When I execute the SQL Statement in the SQL Query Analyzer, I get the
> results
> returned in 4 seconds but when I use the same query from a stored
> procedure
> and execute it from SQL Analyzer, it is taking too long about 50-55
> seconds.
> Am I missing something? I have a join between external and internal table.
>
> Thanks!!
> Vibs
>
> SELECT
> AAPSUser.[ID]
> , AAPSUser.First_Name
> , AAPSUser.Last_Name
> , AAPSUser.Second_Sections,
> [UserExtraInfo].[title],
> [UserExtraInfo].[profile1_text],
> [UserExtraInfo].[profile2_text],
> [UserExtraInfo].[profile3_text]
> FROM
> AAPS.AAPS_Web.DBO.iMIS_Data as AAPSUser
> INNER JOIN UserExtraInfo ON AAPSUser.[ID] = UserExtraInfo.Id
> WHERE
> AAPSUser.[ID] = 003479

21 Oct 2005 12:56 PM

vibs

Yes the query and the proc is running from the same query analyzer window on
the same SQL Server. I'll look into the execution plans
Thanks

Show quote

"Jerry Spivey" wrote:

> Are you running the query and the proc containg the query locally i.e, same
> SQL Server? (Same security creditials as well?) I.e, log into QA and run
> the query then directly below (same window) exectue the proc (same query in
> proc right?). You can also compare the execution plans as well for any
> visable differences.
>
> HTH
>
> Jerry
> "vibs" <v***@discussions.microsoft.com> wrote in message
> news:6DC8F102-2C0F-4CCE-8831-1F228BAF81AC@microsoft.com...
> > When I execute the SQL Statement in the SQL Query Analyzer, I get the
> > results
> > returned in 4 seconds but when I use the same query from a stored
> > procedure
> > and execute it from SQL Analyzer, it is taking too long about 50-55
> > seconds.
> > Am I missing something? I have a join between external and internal table.
> >
> > Thanks!!
> > Vibs
> >
> > SELECT
> > AAPSUser.[ID]
> > , AAPSUser.First_Name
> > , AAPSUser.Last_Name
> > , AAPSUser.Second_Sections,
> > [UserExtraInfo].[title],
> > [UserExtraInfo].[profile1_text],
> > [UserExtraInfo].[profile2_text],
> > [UserExtraInfo].[profile3_text]
> > FROM
> > AAPS.AAPS_Web.DBO.iMIS_Data as AAPSUser
> > INNER JOIN UserExtraInfo ON AAPSUser.[ID] = UserExtraInfo.Id
> > WHERE
> > AAPSUser.[ID] = 003479
>
>
>