Any way to optimise this query?

Hi guys

Is there any way I can run this query faster? Should I take out the ORDER BY
clause? This is supposed to return 17,000 rows and takes around 30 minutes or
so. Is there no way at all to get this result faster?

select r.AttorneyName, r.sitename, r.applicationid, r.clientsurname, r.
clientinitials, r.clientidno, r.grantedamount, r.bankname,
r.accountnumber, r.status, r.grantdate, r.consultantname, r.propertyaddress,
r.erfdescription, r.commenthistory, br.expectedregdate
from bondtrak..rptdetail r
join ebondprd..bankresponse br
on br.applicationid = r.applicationid
where
r.rundate = '20051010'
and r.primarybankind = 'Y'
and r.status = 'granted'
and r.statusdate between '20020101' and '20050930'
and r.businessunit in ('bond choice', 'ppl')
order by r.sitename, r.consultantname, r.statusdate

Thanks for any help.
Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1If you don't need the result ordered then definitely drop the ORDER BY.

Why do you want to return 17,000 rows in one hit anyway? Can't you
process the data server side?

--
David Portas
SQL Server MVP
--|||Do you have any usefull indexes on these tables?

"I sense many useless updates in you... Useless updates lead to
defragmentation... Defragmentation leads to downtime...Downtime leads
to suffering..Defragmentation is the path to the darkside.. DBCC
INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with
you" -- http://sqlservercode.blogspot.com/|||Driesen via SQLMonster.com (u11907@.uwe) writes:
> Is there any way I can run this query faster? Should I take out the
> ORDER BY clause? This is supposed to return 17,000 rows and takes around
> 30 minutes or so. Is there no way at all to get this result faster?
> select r.AttorneyName, r.sitename, r.applicationid, r.clientsurname, r.
> clientinitials, r.clientidno, r.grantedamount, r.bankname,
> r.accountnumber, r.status, r.grantdate, r.consultantname,
> r.propertyaddress,
> r.erfdescription, r.commenthistory, br.expectedregdate
> from bondtrak..rptdetail r
> join ebondprd..bankresponse br
> on br.applicationid = r.applicationid
> where
> r.rundate = '20051010'
> and r.primarybankind = 'Y'
> and r.status = 'granted'
> and r.statusdate between '20020101' and '20050930'
> and r.businessunit in ('bond choice', 'ppl')
> order by r.sitename, r.consultantname, r.statusdate

There might be. But without knowledge of the tables, indexes and how
big they are, all you can get is guesses. If you don't need data to be
sorted, you can remove ORDER BY, but it doesn't take 29 minutes to sort
17000 rows, so the effect is moderate of that operation.

A clustered index on rptdetail(rundste, statusdate) or only (rundate)
should be a good start. An index on bankreponse(applicationid) is also
necessary.

You may also have problems with statistics that are out of date.
UPDATE STATISTICS WITH FULLSCAN on both table can address this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi David

I dropped the ORDER BY and copied it Excel. This is only a once off query for
the big wigs.

Thanks for the help
Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1|||Hi Erland

Thanks for the help. I did drop the ORDER BY. The only reason this takes so
long to run is becasue our DataBase sits over 500 miles away. We to get
through a whole lot of banking environment security to access our DB.

Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1|||> We to get
> through a whole lot of banking environment security to access our DB.

So you thought you'd undermine all that security by copying the data to
an insecure Excel spreadsheet... :-)

Maybe you can use DTS to export the data to Excel. Or use BCP to create
a delimited file that can be opened in Excel.

--
David Portas
SQL Server MVP
--

Any unique datetime's in SQL2005?

Once upon a time, some version of SQLServer was supposed to do
timestamps as datetimes, so you could have a guaranteed unique token
that decoded to a real date/time. I believe this was supposed to be
the ANSI standard for SQL as well.
Has this yet been delivered in SQL2005?
(I think not, but am not certain, and would like to have it for
something I'm doing right now! I should have SQL2005 loaded here in a
few days, but thanks in advance for any answer to this. btw, I don't
see the SQL2005 documentation yet online at MSDN.microsoft.com)
J.No.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:quqpo15ejq4biu6cgsg2nkiedr92dinok1@.
4ax.com...
> Once upon a time, some version of SQLServer was supposed to do
> timestamps as datetimes, so you could have a guaranteed unique token
> that decoded to a real date/time. I believe this was supposed to be
> the ANSI standard for SQL as well.
> Has this yet been delivered in SQL2005?
> (I think not, but am not certain, and would like to have it for
> something I'm doing right now! I should have SQL2005 loaded here in a
> few days, but thanks in advance for any answer to this. btw, I don't
> see the SQL2005 documentation yet online at MSDN.microsoft.com)
> J.
>

Any unique datetime's in SQL2005?

Once upon a time, some version of SQLServer was supposed to do
timestamps as datetimes, so you could have a guaranteed unique token
that decoded to a real date/time. I believe this was supposed to be
the ANSI standard for SQL as well.
Has this yet been delivered in SQL2005?
(I think not, but am not certain, and would like to have it for
something I'm doing right now! I should have SQL2005 loaded here in a
few days, but thanks in advance for any answer to this. btw, I don't
see the SQL2005 documentation yet online at MSDN.microsoft.com)
J.No. :(
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:quqpo15ejq4biu6cgsg2nkiedr92dinok1@.4ax.com...
> Once upon a time, some version of SQLServer was supposed to do
> timestamps as datetimes, so you could have a guaranteed unique token
> that decoded to a real date/time. I believe this was supposed to be
> the ANSI standard for SQL as well.
> Has this yet been delivered in SQL2005?
> (I think not, but am not certain, and would like to have it for
> something I'm doing right now! I should have SQL2005 loaded here in a
> few days, but thanks in advance for any answer to this. btw, I don't
> see the SQL2005 documentation yet online at MSDN.microsoft.com)
> J.
>

Any unique datetime's in SQL2005?

Once upon a time, some version of SQLServer was supposed to do
timestamps as datetimes, so you could have a guaranteed unique token
that decoded to a real date/time. I believe this was supposed to be
the ANSI standard for SQL as well.
Has this yet been delivered in SQL2005?
(I think not, but am not certain, and would like to have it for
something I'm doing right now! I should have SQL2005 loaded here in a
few days, but thanks in advance for any answer to this. btw, I don't
see the SQL2005 documentation yet online at MSDN.microsoft.com)
J.
No.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:quqpo15ejq4biu6cgsg2nkiedr92dinok1@.4ax.com...
> Once upon a time, some version of SQLServer was supposed to do
> timestamps as datetimes, so you could have a guaranteed unique token
> that decoded to a real date/time. I believe this was supposed to be
> the ANSI standard for SQL as well.
> Has this yet been delivered in SQL2005?
> (I think not, but am not certain, and would like to have it for
> something I'm doing right now! I should have SQL2005 loaded here in a
> few days, but thanks in advance for any answer to this. btw, I don't
> see the SQL2005 documentation yet online at MSDN.microsoft.com)
> J.
>

Any idea what the purpose of this SPROC might be? (no prizes, sorry!)

I've been asked to document an application and I'm going through all
the Stored Procedures and trying to work out what they're supposed to
do.

Can anyone give me an idea of what the Stored Procedure
wsBookingListsGetAll below is trying to achieve? Is it incomplete? I
can't see any reason to pass in the Parameter, and what is the UNION
SELECT 0 all about?

Many thanks

Edward

CREATE Procedure wsBookingListsGetAll
@.DebtorIDvarchar(15)
As
set nocount on

SELECT
fldBookingListID
FROM
tblWsBookingList
UNION
SELECT 0

return

GO

/* Table def */
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblWSBookingList]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblWSBookingList]
GO

CREATE TABLE [dbo].[tblWSBookingList] (
[fldDebtorID] [char] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldBookingName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
,
[fldAddressCode] [char] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldEmail] [varchar] (250) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldFirstName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldLastName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldBookingListID] [int] IDENTITY (1, 1) NOT NULL ,
[fldInvoiceNumber] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[fldPayeeID] [char] (15) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GOHi Edward,

It is obvious the SP is returning list of booking list ids. Maybe there are
two scenarios:

1). Incomplete: The SP parameter was intended to filter the list by debtor
id but the code was never completed.
2). Obsolete: In the past the parameter was used properly in the SP but
something required to return the full list all the time and the condition
was dropped from the SQL code. Since the parameter may be passed from the
client application, the developer did not bother to change properly the
client code to drop the parameter, but rather did the change only in the SQL
code and did not comment the change.

As for the union with SELECT 0 it seems like the return list required a
placeholder for some special value (maybe in the client application the list
needs to show "Not Selected" which will be mapped to the 0 value).

Probably the best would be to look at the places where this SP is called and
that will help you to figure out the reasons.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

ANSI vs SQL 92 ambiguity ?!? yeah right

manstein,
I must admit that I did not thoroughly read your posting.
However, the "supposed" ambiguity of the old outer join syntax (such as
*=) is well known and documented. Here is one posting that illustrates
the ambiguity, and highlights this by sharing with the rest of the world
how different RDBMS vendors (at the time) had implemented this syntax:
differently! The posting almost dates back to the last century.
See
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/518d849c9b1040c0?dmode=source
(url may warp)
Gert-Jan
manstein wrote:
> I have uncovered some interesting techniques that I wanted to share
> with everyone. In particular, they relate to the claimed "ambiguity"
> between using the ANSI outer join operators (LEFT OUTER JOIN, RIGHT
> OUTER JOIN) over *= syntax by Microsoft. A little background would be
> helpful. Microsoft has stated that it will not be supporting the
> older *= syntax into the future. The claim is that it poses ambiguity
> when adding filter logic to the left outer join query. If the filter
> logic is added to the FROM clause, the LEFT OUTER JOIN is preserved.
> In contrast, filter logic added to the WHERE clause actually turns a
> left outer join into an inner join. Example:
> CREATE TABLE #foo (indx INT IDENTITY(1,1), taste VARCHAR(255));
> CREATE TABLE #bar (indx INT IDENTITY(1,1), taste VARCHAR(255));
> INSERT INTO #foo (taste) VALUES ('yummy!');
> INSERT INTO #foo (taste) VALUES ('bummy!');
> INSERT INTO #foo (taste) VALUES ('mummy!');
> INSERT INTO #bar (taste) VALUES ('yummy!');
> INSERT INTO #bar (taste) VALUES ('tummy!');
> INSERT INTO #bar (taste) VALUES ('mummy!');
> --This will return all records from dbo.foo with matching records
> from
> --dbo.bar filtered by b.COL1 = "yummy!" non matching dbo.bar records
> will
> --produce NULLS.
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste AND
> b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> 3 mummy! NULL NULL
> -- This will only return records from dbo.foo that have a matching
> -- dbo.bar record where b.COL1 = "yummy!" In other words, this
> produces
> -- an inner join styled "filter"
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste
> WHERE
> b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> This illustration highlights the fact that filter logic in the FROM
> clause is applied before the join is made while filter logic in the
> WHERE clause is applied after the join. This is a characteristic of
> Microsoft's preferred syntax. With the older SQL 92 syntax, we have a
> different flavor. The logic in the WHERE clause also preserves our
> LEFT outer join with the addition of filters. Example:
> SELECT
> *
> FROM
> #foo f,
> #bar b
> WHERE
> f.taste *= b.taste
> AND b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> 3 mummy! NULL NULL
> In my opinion, there is no ambiguity at all. If you are familiar with
> the peculiarities of these two competing styles, you can get the
> results you want. Certainly the query and result themselves are not
> ambiguous to the system. Now, since it is sometimes desirable in
> practice to be able to apply filter logic to a relation *after* two
> tables have been left outer joined (in particular, in WHERE clause
> subqueries), I was curious to test the limits of MS claim. What I
> found is that you can have WHERE clause filter logic with ANSI outer
> join operators but with a twist. The trick is to add an additional
> filter which includes NULL values from your joined table. Example:
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste
> WHERE
> b.taste = 'yummy!' OR
> b.taste IS NULL
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> Here we get a different result. Tuples matching my filter value
> 'yummy!' are included as well as tuples that have no matching value
> and produce NULL. The twist is rows that do have matching values in
> #bar that are not filtered for (i.e. 'mummy!') are excluded. Also,
> there is an addition drawback. If #.bar COL1 has an index defined on
> it, the system will perform an index scan as opposed to a seek.
> Nevertheless, this approach can be a significant improvement on an
> order of magnitude better if the only alternative is line - by -line
> processing.
> As of this writing, older SQL 92 syntax is unsupported in SQL Server
> 2005 level 9.0. With MS's history of dealing with competing
> technologies, I suspect there is more than meets the eye with
> Microsoft's witch hunt against *= syntax beyond the claim of ambiguity
> but I digress.
My favorite is a table (*) with 5 rows that has NULL for a column. And a query that say col IS NULL
now suddenly returns 8 rows with NULL. Where did the 3 non-existing rows come from? Or a query
saying col IS NOT NULL returning 5 rows with NULL for that column? Or the fact that these queries
doesn't return the same result running on 2000 vs 2005 (with compatibility mode 80).
(*) The table is in fact a view, but a view should behave like a table...
Run below in 2000:
USE pubs
GO
EXEC sp_dbcmptlevel pubs, 80
GO
IF OBJECT_ID('v') IS NOT NULL
DROP VIEW v
GO
CREATE VIEW v AS
SELECT p.pub_name, t.price, t.title
FROM publishers AS p, titles AS t
WHERE p.pub_id *= t.pub_id
AND t.price IS NOT NULL -- *** OBS ***
GO
--begrunda nedanstende resultat:
SELECT *
FROM v
SELECT *
FROM v
WHERE price IS NULL
SELECT *
FROM v
WHERE price IS NOT NULL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:46366ED0.C1FE40F3@.toomuchspamalready.nl...[vbcol=seagreen]
> manstein,
> I must admit that I did not thoroughly read your posting.
> However, the "supposed" ambiguity of the old outer join syntax (such as
> *=) is well known and documented. Here is one posting that illustrates
> the ambiguity, and highlights this by sharing with the rest of the world
> how different RDBMS vendors (at the time) had implemented this syntax:
> differently! The posting almost dates back to the last century.
> See
> http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/518d849c9b1040c0?dmode=source
> (url may warp)
> Gert-Jan
>
>
> manstein wrote:
|||"Gert-Jan Strik" wrote:

> *=) is well known and documented. Here is one posting that illustrates
> the ambiguity, and highlights this by sharing with the rest of the world
> how different RDBMS vendors (at the time) had implemented this syntax:
> differently! The posting almost dates back to the last century.
The argument in this post is specious. It is based entirely on the statement
that some implementations of *= are broken. That argument can be equally
applied against LEFT OUTER JOIN, obviously. The spelling mistakes and
obviously overblown statements make me dismiss it out of hand. According to
the post, *= is "simpoly dead wrong and dangerous". OK c00l d00d.
Maury