Huge query is very slow! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Huge query is very slow!

Author

Topic

raxbat
Yak Posting Veteran

52 Posts

Posted - 2008-01-24 : 04:52:54

Hello experts! I have huge union query which run very slow! How to increase its speed of execution? Thanx!

SELECT '' AS PlombasNr, SUM(ATM_inkasacija.AtlikumsAprekins) AS Summa, ATM_inkasacija.Valuta, Objekti.ObjektaNr AS Objekts, 'ATM' AS Tips,
'' AS datums1
FROM ATM_inkasacija INNER JOIN
Objekti ON ATM_inkasacija.ATM = Objekti.ObjektaID INNER JOIN
Lietotaji ON ATM_inkasacija.AutorsInk = Lietotaji.id
WHERE (ATM_inkasacija.InkBanka = 'DnB Nord') AND (ATM_inkasacija.IzdDatums >= CONVERT(DATETIME, '2008-01-22 00:00:00', 102)) AND
(ATM_inkasacija.IzdDatums < CONVERT(DATETIME, '2008-02-24 00:00:00', 102)) AND (Lietotaji.Filiale = 'RL')
GROUP BY ATM_inkasacija.Valuta, Objekti.ObjektaNr
UNION
SELECT '' AS PlombasNr, SUM(ATM_inkasacija.TestaNauda) AS Summa, ATM_inkasacija.Valuta, Objekti.ObjektaNr AS Objekts, 'TN' AS Tips,
'' AS datums1
FROM ATM_inkasacija INNER JOIN
Objekti ON ATM_inkasacija.ATM = Objekti.ObjektaID INNER JOIN
Lietotaji ON ATM_inkasacija.AutorsTesta = Lietotaji.id
WHERE (ATM_inkasacija.InkBanka = 'DnB Nord') AND (ATM_inkasacija.DatumsTesta >= CONVERT(DATETIME, '2008-01-22 00:00:00', 102)) AND
(ATM_inkasacija.DatumsTesta < CONVERT(DATETIME, '2008-02-24 00:00:00', 102)) AND (Lietotaji.Filiale = 'RL')
GROUP BY ATM_inkasacija.Valuta, Objekti.ObjektaNr
UNION
SELECT '' AS PlombasNr, SUM(STOP_sub.Summa) AS Summa, STOP_sub.Valuta, 'INK-' AS Objekts, 'INKASACIJA' AS Tips, '' AS datums1
FROM STOP INNER JOIN
STOP_sub ON STOP.id = STOP_sub.stop INNER JOIN
Objekti ON STOP.ObjektaNr = Objekti.ObjektaNr INNER JOIN
Klienti ON Objekti.KlientaNr = Klienti.KlientaNr INNER JOIN
Lietotaji ON STOP.Autors = Lietotaji.id
WHERE (Klienti.KlientaNr <> '0000') AND (Objekti.Tips <> 3 OR
Objekti.Tips IS NULL) AND (STOP.Datums >= CONVERT(DATETIME, '2008-01-22 00:00:00', 102)) AND (STOP.Datums < CONVERT(DATETIME,
'2008-02-24 00:00:00', 102)) AND (STOP.InkBanka = 'DnB Nord') AND (Lietotaji.Filiale = 'RL')
GROUP BY STOP_sub.Valuta
UNION
SELECT '' AS PlombasNr, SUM(- MNauda_sub.Summa) AS Summa, MNauda.Valuta, 'MN' AS Objekts, 'INKASACIJA' AS Tips, '' AS datums1
FROM MNauda INNER JOIN
MNauda_sub ON MNauda.id = MNauda_sub.DocId INNER JOIN
Lietotaji ON MNauda.Autors = Lietotaji.id
WHERE (MNauda.DateDoc >= CONVERT(DATETIME, '2008-01-22 00:00:00', 102) AND MNauda.DateDoc < CONVERT(DATETIME, '2008-02-24 00:00:00', 102)) AND
(MNauda.InkBanka = 'DnB Nord') AND (MNauda.stop > 0) AND (Lietotaji.Filiale = 'RL')
GROUP BY MNauda.Valuta
UNION
SELECT '' AS PlombasNr, SUM(STOP_sub.Summa) AS Summa, STOP_sub.Valuta, Objekti.Nosaukums AS Objekts, 'G4F INK' AS Tips, '' AS datums1
FROM STOP INNER JOIN
STOP_sub ON STOP.id = STOP_sub.stop INNER JOIN
Objekti ON STOP.ObjektaNr = Objekti.ObjektaNr INNER JOIN
Klienti ON Objekti.KlientaNr = Klienti.KlientaNr INNER JOIN
Lietotaji ON STOP.Autors = Lietotaji.id
WHERE (Klienti.KlientaNr = '0000') AND (STOP.Datums >= CONVERT(DATETIME, '2008-01-22 00:00:00', 102)) AND (STOP.Datums < CONVERT(DATETIME,
'2008-02-24 00:00:00', 102)) AND (STOP.InkBanka = 'DnB Nord') AND (Lietotaji.Filiale = 'RL')
GROUP BY STOP_sub.Valuta, Objekti.Nosaukums
UNION
SELECT stop.plombasnr, SUM(STOP_sub.Summa) AS Summa, STOP_sub.Valuta, Objekti.Nosaukums AS Objekts, 'FILIALES' AS Tips,
dbo.FilDatums(STOP.PlombasNr) AS datums1
FROM STOP INNER JOIN
STOP_sub ON STOP.id = STOP_sub.stop INNER JOIN
Objekti ON STOP.ObjektaNr = Objekti.ObjektaNr INNER JOIN
Lietotaji ON STOP.Autors = Lietotaji.id
WHERE (Objekti.Tips = 3) AND (STOP.Datums >= CONVERT(DATETIME, '2008-01-22 00:00:00', 102)) AND (STOP.Datums < CONVERT(DATETIME,
'2008-02-24 00:00:00', 102)) AND (STOP.InkBanka = 'DnB Nord') AND (Lietotaji.Filiale = 'RL')
GROUP BY stop.plombasnr, STOP_sub.Valuta, Objekti.Nosaukums, dbo.FilDatums(STOP.PlombasNr)
UNION
SELECT '' AS PlombasNr, SUM(GKuz_sub.Summa) AS summa, GKuz_sub.Valuta, GKuz.Piegadatajs AS objekts, 'LB' AS tips, '' AS datums1
FROM GKuz INNER JOIN
GKuz_sub ON GKuz.ID = GKuz_sub.DokID INNER JOIN
Lietotaji ON GKuz.Autors = Lietotaji.id
WHERE (GKuz.Piegadatajs = 'LB') AND (GKuz.InkBanka = 'DnB Nord') AND (GKuz.DatumsAkceptets >= CONVERT(DATETIME, '2008-01-22 00:00:00', 102)) AND
(GKuz.DatumsAkceptets < CONVERT(DATETIME, '2008-02-24 00:00:
00', 102)) AND (Lietotaji.Filiale = 'RL')
GROUP BY GKuz_sub.Valuta, GKuz.Piegadatajs
UNION
SELECT '' AS PlombasNr, SUM(GKuz_sub.Summa) AS summa, GKuz_sub.Valuta, GKuz.Piegadatajs AS objekts, 'PAREX' AS tips, '' AS datums1
FROM GKuz INNER JOIN
GKuz_sub ON GKuz.ID = GKuz_sub.DokID INNER JOIN
Lietotaji ON GKuz.Autors = Lietotaji.id
WHERE (GKuz.Piegadatajs = 'AIR') AND (GKuz.InkBanka = 'DnB Nord') AND (GKuz.DatumsAkceptets >= CONVERT(DATETIME, '2008-01-22 00:00:00', 102)) AND
(GKuz.DatumsAkceptets < CONVERT(DATETIME, '2008-02-24 00:00:00', 102)) AND (Lietotaji.Filiale = 'RL')
GROUP BY GKuz_sub.Valuta, GKuz.Piegadatajs
ORDER BY tips

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-24 : 07:13:15

Would you be able to post the Execution Plan? Do you have unnecessary scans going? Are your statistics up to date? What are the indices which this query uses?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

raxbat
Yak Posting Veteran

52 Posts

Posted - 2008-01-24 : 07:15:58

What kind of statistics?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-01-24 : 07:20:04

table statistics....system generated summaries of how each table data is internally organised.
will aid the SQL Engine guess as to the fastest way to collect your data.
can be generated on request or via a maintainence job.

raxbat
Yak Posting Veteran

52 Posts

Posted - 2008-01-24 : 07:23:09

wow! how can I generate this statistics?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-01-24 : 11:17:45

1. use BOL and search for info on Statistics...there's a lot of help in the help system that comes with SQL Server.
2. investigate/use the following T-SQL code, which comes from BOL.

Transact-SQL Reference

CREATE STATISTICS
Creates a histogram and associated density groups (collections) over the supplied column or set of columns.

Syntax
CREATE STATISTICS statistics_name
ON { table | view } ( column [ ,...n ] )
[ WITH
[ [ FULLSCAN
| SAMPLE number { PERCENT | ROWS } ] [ , ] ]
[ NORECOMPUTE ]
]

Arguments
statistics_name

Is the name of the statistics group to create. Statistics names must conform to the rules for identifiers.

table

Is the name of the table on which to create the named statistics. Table names must conform to the rules for identifiers. table is the table with which the column is associated. Specifying the table owner name is optional. Statistics can be created on tables in another database by specifying a qualified database name.

view

Is the name of the view on which to create the named statistics. A view must have a clustered index before statistics can be created on it. View names must conform to the rules for identifiers. view is the view with which the column is associated. Specifying the view owner name is optional. Statistics can be created on views in another database by specifying a qualified database name.

column

Is the column or set of columns on which to create statistics. Computed columns and columns of the ntext, text, or image data types cannot be specified as statistics columns.

n

Is a placeholder indicating that multiple columns can be specified.

FULLSCAN

Specifies that all rows in table should be read to gather the statistics. Specifying FULLSCAN provides the same behavior as SAMPLE 100 PERCENT. This option cannot be used with the SAMPLE option.

SAMPLE number { PERCENT | ROWS }

Specifies that a percentage, or a specified number of rows, of the data should be read using random sampling to gather the statistics. number can be only an integer: if PERCENT, number should be from 0 through 100; if ROWS, number can be from 0 to the n total rows.

This option cannot be used with the FULLSCAN option. If no SAMPLE or FULLSCAN option is given, an automatic sample is computed by Microsoft® SQL Server™.

NORECOMPUTE

Specifies that automatic recomputation of the statistics should be disabled. If this option is specified, SQL Server continues to use previously created (old) statistics even as the data changes. The statistics are not automatically updated and maintained by SQL Server, which may produce suboptimal plans.

Warning It is recommended that this option be used rarely and only by a trained system administrator.

Remarks
Only the table owner can create statistics on that table. The owner of a table can create a statistics group (collection) at any time, whether or not there is data in the table.

CREATE STATISTICS can be executed on views with clustered index, or indexed views. Statistics on indexed views are used by the optimizer only if the view is directly referenced in the query and the NOEXPAND hint is specified for the view. Otherwise, the statistics are derived from the underlying tables before the indexed view is substituted into the query plan. Such substitution is supported only on Microsoft SQL Server 2000 Enterprise and Developer Editions.

Permissions
CREATE STATISTICS permissions default to members of the sysadmin fixed server role and the db_ddladmin and db_owner fixed database roles and the table owner, and are not transferable.

Examples
A. Use CREATE STATISTICS with SAMPLE number PERCENT
This example creates the names statistics group (collection), which calculates random sampling statistics on five percent of the CompanyName and ContactName columns of the Customers table.

CREATE STATISTICS names
ON Customers (CompanyName, ContactName)
WITH SAMPLE 5 PERCENT
GO

B. Use CREATE STATISTICS with FULLSCAN and NORECOMPUTE
This example creates the names statistics group (collection), which calculates statistics for all rows in the CompanyName and ContactName columns of the Customers table and disables automatic recomputation of statistics.

CREATE STATISTICS names
ON Northwind..Customers (CompanyName, ContactName)
WITH FULLSCAN, NORECOMPUTE
GO

See Also
CREATE INDEX
DBCC SHOW_STATISTICS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources