help tune view

Last Post 14 Aug 2007 03:34 AM by river1. 13 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
river1
New Member
New Member

--
09 Aug 2007 10:34 PM
Hi-

I'm having dificulties in tunning this view.
This view can´t be a indexed view because it have must agregation.
Can someone help me ?






create view dbo.teste2


AS
SELECT CO.CODCTB AS CODCTB, CO.NIFCTB AS NIFCTB, CO.NOMECTB AS NOMECTB, CO.MORADACTB AS MORADACTB,LEFT(CO.MORADACTB,120) AS MORADACTB_TRUNC, CO.LOCCTB AS LOCCTB, (SELECT DES_BAIRRO FROM dbo.BAIRRO WHERE CO.CODBAIRRO = dbo.BAIRRO.COD_BAIRRO) AS COMUNA, (SELECT IDMUNIC FROM dbo.MUNICIPIO WHERE CO.CODMUNIC = MUNICIPIO.CODMUNIC) AS MUNICIPIO, (SELECT DESCIDADE FROM dbo.CIDADES WHERE CO.CODCIDADE = CIDADES.CODCIDADE) AS CIDADECTB,
CO.CODRF AS CODRF, CO.CODCAE AS CODCAE, CO.CODPROV AS CODPROV, CO.STATUSCTB AS STATUSCTB, CO.CODRTRIB AS CODRTRIB,
CO.CODMUNIC AS CODMUNIC, CONVERT(DATETIME,CO.DTAICTB) AS DTAICTB, CONVERT(DATETIME,CO.DTACCTB) AS DTACCTB, CO.CODENQUAD AS CODENQUAD,
CO.CODSECTOR AS CODSECTOR, 5 AS TIPO, CONVERT(DATETIME,NULL) AS DTACESSACAOTCO, CONVERT(DATETIME,NULL) AS DTACESSACAOTCP, CONVERT(DATETIME,NULL) AS DTACESSACAOA,
0 AS PSERVTCO, 0 AS PSERVTCP, 0 AS PSERVA, (SELECT MIN(CONVERT(DATETIME,CD.DATAH))FROM dbo.H_CONTRIBUINTEE CD WHERE CO.CODCTB=CD.CODCTB) AS DATAINSERE
FROM dbo.CONTRIBUINTEE CO
UNION
SELECT CI.CODCTB AS CODCTB, CI.NIFCTB AS NIFCTB, CI.NOMECTB AS NOMECTB, CI.MORADACTB AS MORADACTB,LEFT(CI.MORADACTB,120) AS MORADACTB_TRUNC, CI.LOCCTB AS LOCCTB, (SELECT DES_BAIRRO FROM dbo.BAIRRO WHERE CI.CODBAIRRO = dbo.BAIRRO.COD_BAIRRO) AS COMUNA, (SELECT IDMUNIC FROM dbo.MUNICIPIO WHERE CI.CODMUNIC = dbo.MUNICIPIO.CODMUNIC) AS MUNICIPIO, (SELECT DESCIDADE FROM dbo.CIDADES WHERE CI.CODCIDADE = dbo.CIDADES.CODCIDADE) AS CIDADECTB,
CI.CODRF AS CODRF, NULL AS CODCAE, CI.CODPROV AS CODPROV, NULL AS STATUSCTB, NULL AS CODRTRIB, CI.CODMUNIC AS CODMUNIC,
CONVERT(DATETIME,CI.DTAICTB) AS DTAICTB, CONVERT(DATETIME,CI.DTACCTB) AS DTACCTB, 10 AS CODENQUAD, NULL AS CODSECTOR, 7 AS TIPO, CONVERT(DATETIME,NULL) AS DTACESSACAOTCO, CONVERT(DATETIME,NULL)
AS DTACESSACAOTCP, CONVERT(DATETIME,NULL) AS DTACESSACAOA, 0 AS PSERVTCO, 0 AS PSERVTCP, 0 AS PSERVA, (SELECT MIN(CONVERT(DATETIME,CD.DATAH))FROM dbo.H_CONTRIBUINTEINST CD WHERE CI.CODCTB=CD.CODCTB) AS DATAINSERE
FROM dbo.CONTRIBUINTEINST CI
UNION
SELECT CE.CODCTB AS CODCTB, CE.NIFCTB AS NIFCTB, CE.NOMECTB AS NOMECTB, CE.MORADACTB AS MORADACTB,LEFT(CE.MORADACTB,120) AS MORADACTB_TRUNC, CE.LOCCTB AS LOCCTB, (SELECT DES_BAIRRO FROM dbo.BAIRRO WHERE CE.CODBAIRRO = dbo.BAIRRO.COD_BAIRRO) AS COMUNA, (SELECT IDMUNIC FROM dbo.MUNICIPIO WHERE CE.CODMUNIC = dbo.MUNICIPIO.CODMUNIC) AS MUNICIPIO, (SELECT DESCIDADE FROM dbo.CIDADES WHERE CE.CODCIDADE = dbo.CIDADES.CODCIDADE) AS CIDADECTB,
CE.CODRF AS CODRF, CE.CODCAE AS CODCAE, CE.CODPROV AS CODPROV, CE.STATUSCTB AS STATUSCTB, CE.CODRTRIB AS CODRTRIB,
CE.CODMUNIC AS CODMUNIC, CONVERT(DATETIME,CE.DTAICTB) AS DTAICTB, CONVERT(DATETIME,CE.DTACCTB) AS DTACCTB, 9 AS CODENQUAD, CE.CODSECTOR AS CODSECTOR, CAST(LEFT(CE.NIFCTB,1)as INT) AS TIPO,
CONVERT(DATETIME,CE.DTACESSACAOTCO) AS DTACESSACAOTCO, CONVERT(DATETIME,CE.DTACESSACAOTCP) AS DTACESSACAOTCP, CONVERT(DATETIME,CE.DTACESSACAOA) AS DTACESSACAOA, CE.PSERVTCO AS PSERVTCO, CE.PSERVTCP AS PSERVTCP,
CE.PSERVA AS PSERVA, (SELECT MIN(CONVERT(DATETIME,CD.DATAH))FROM dbo.H_CONTRIBUINTESEST CD WHERE CE.CODCTB=CD.CODCTB) AS DATAINSERE
FROM dbo.CONTRIBUINTESEST CE


This view the first time that is executed takes 20 secunds, the secund time takes 10 sec(the rest of the times take 10 sec.)
I have tried to pass this view to a stored procedure , but the time is the same.


I have made tests(the test was made with the database on my personal computer, and i was the only person doing this select) and i notice that the 3º union is the the query that takes time.
I have made a select to the table CONTRIBUINTESEST like this:

select * from contribuinte
SQLUSA
New Member
New Member

--
10 Aug 2007 10:42 PM
Make sure all the columns are indexed which are used in the JOINs and WHEREs.

Also, change columns (or add new columns) so you don't have to use CONVERTs.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005grandslam/ - The Best SQL Server 2005 Training
river1
New Member
New Member

--
11 Aug 2007 01:49 AM
Hi-SQLUSA
Thank you for your reply.

I have traied to do what you told me but i still have a problem.
The problem is , as i said, that when i make a simple select to this table (tablem name -contribuintesest) that have 158.000 rows (records), this simple query (select * from contribuintesest), takes 7 sec. to execute.
How can i decrease de select time to this table , without having to buy more hardware (the company does allow).

This table have a primary key(codctb) clusetered index and some non -clustered indexes.


Best Regards,

Pedro
river1
New Member
New Member

--
12 Aug 2007 10:51 PM
Hi- Pro-peter.

The result of the DBCC is:

DBCC SHOWCONTIG scanning 'CONTRIBUINTESEST' table...
Table: 'CONTRIBUINTESEST' (510624862); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 11080
- Extents Scanned..............................: 1394
- Extent Switches..............................: 1393
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.35% [1385:1394]
- Extent Scan Fragmentation ...................: 75.75%
- Avg. Bytes Free per Page.....................: 1266.2
- Avg. Page Density (full).....................: 84.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

does this mean that i have to make a index defrag?
I dont know who to interpret this results.
Can someone commet this results?

Best Regards,

Pedro
river1
New Member
New Member

--
14 Aug 2007 03:34 AM
Hi-

I executed the command with "all indexs"
the result was this:


DBCC SHOWCONTIG scanning 'CONTRIBUINTESEST' table...
Table: 'CONTRIBUINTESEST' (510624862); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 11080
- Extents Scanned..............................: 1394
- Extent Switches..............................: 1393
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.35% [1385:1394]
- Extent Scan Fragmentation ...................: 75.75%
- Avg. Bytes Free per Page.....................: 1265.8
- Avg. Page Density (full).....................: 84.36%
DBCC SHOWCONTIG scanning 'CONTRIBUINTESEST' table...
Table: 'CONTRIBUINTESEST' (510624862); index ID: 2, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 293
- Extents Scanned..............................: 109
- Extent Switches..............................: 135
- Avg. Pages per Extent........................: 2.7
- Scan Density [Best Count:Actual Count].......: 27.21% [37:136]
- Logical Scan Fragmentation ..................: 5.12%
- Extent Scan Fragmentation ...................: 99.08%
- Avg. Bytes Free per Page.....................: 35.6
- Avg. Page Density (full).....................: 99.56%
DBCC SHOWCONTIG scanning 'CONTRIBUINTESEST' table...
Table: 'CONTRIBUINTESEST' (510624862); index ID: 3, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 1318
- Extents Scanned..............................: 222
- Extent Switches..............................: 1256
- Avg. Pages per Extent........................: 5.9
- Scan Density [Best Count:Actual Count].......: 13.13% [165:1257]
- Logical Scan Fragmentation ..................: 47.12%
- Extent Scan Fragmentation ...................: 99.55%
- Avg. Bytes Free per Page.....................: 2622.5
- Avg. Page Density (full).....................: 67.60%
DBCC SHOWCONTIG scanning 'CONTRIBUINTESEST' table...
Table: 'CONTRIBUINTESEST' (510624862); index ID: 4, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 846
- Extents Scanned..............................: 165
- Extent Switches..............................: 639
- Avg. Pages per Extent........................: 5.1
- Scan Density [Best Count:Actual Count].......: 16.56% [106:640]
- Logical Scan Fragmentation ..................: 52.13%
- Extent Scan Fragmentation ...................: 98.18%
- Avg. Bytes Free per Page.....................: 4001.7
- Avg. Page Density (full).....................: 50.56%
DBCC SHOWCONTIG scanning 'CONTRIBUINTESEST' table...
Table: 'CONTRIBUINTESEST' (510624862); index ID: 5, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 2235
- Extents Scanned..............................: 339
- Extent Switches..............................: 2223
- Avg. Pages per Extent........................: 6.6
- Scan Density [Best Count:Actual Count].......: 12.59% [280:2224]
- Logical Scan Fragmentation ..................: 49.80%
- Extent Scan Fragmentation ...................: 99.12%
- Avg. Bytes Free per Page.....................: 2608.7
- Avg. Page Density (full).....................: 67.77%
DBCC SHOWCONTIG scanning 'CONTRIBUINTESEST' table...
Table: 'CONTRIBUINTESEST' (510624862); index ID: 6, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 1322
- Extents Scanned..............................: 221
- Extent Switches..............................: 1262
- Avg. Pages per Extent........................: 6.0
- Scan Density [Best Count:Actual Count].......: 13.14% [166:1263]
- Logi
river1
New Member
New Member

--
14 Aug 2007 05:14 AM
A didn't yet rebuild.
I will rebuild then and then i will send the result of the time of query , after the rebuild.
This database is a database of insert/updates/ deletes OLPT.
Who often should i rebuild my indexs?


Best regards,

Pedro
river1
New Member
New Member

--
14 Aug 2007 06:52 AM
Thank you very mutch RM.

What is the influency of the update statistics on a database?
river1
New Member
New Member

--
14 Aug 2007 07:12 AM
Hi

I have runed the command:


dbcc dbreindex ('contribuintesest')

then made the select * from contribuintesest again (query takes 1 secund less).
I have made the :


from 9 sec. to 8 sec.


then i made a UPDATE STATISTICS contribuintesest , but the time did not change.
Don´t understand very well what does this command do.
river1
New Member
New Member

--
14 Aug 2007 09:11 AM
Hi-OLU,

Sorry for the 2 posts, i will not do that again.
river1
New Member
New Member

--
14 Aug 2007 09:16 AM
Hi -OLU,

This is the result:


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 6 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(157446 row(s) affected)

Table 'CONTRIBUINTESEST'. Scan count 5, logical reads 74210, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1152 ms, elapsed time = 8057 ms.
SQLUSA
New Member
New Member

--
14 Aug 2007 04:04 PM
The indexes are badly fragmented. Example:

- Scan Density [Best Count:Actual Count].......: 12.59% [280:2224]
- Logical Scan Fragmentation ..................: 49.80%


Reindex the table, if dynamic use FILL FACTOR 70. (dbcc dbreindex...)

You should have a clustered index if you are doing range searches.

Kalman Toth, Database Architect
SQL Server 2005 Training - http://www.sqlusa.com


river1
New Member
New Member

--
15 Aug 2007 06:01 AM
Hi- thank you for your help.


I have made the command.
The result after creating the clustered index on column codctb(primary key of table) is:


DBCC SHOWCONTIG scanning 'CONTRIBUINTESEST' table...
Table: 'CONTRIBUINTESEST' (510624862); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 10049
- Extents Scanned..............................: 1259
- Extent Switches..............................: 1258
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.84% [1257:1259]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 74.11%
- Avg. Bytes Free per Page.....................: 569.5
- Avg. Page Density (full).....................: 92.96%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


But the time of the select didn't get better.


Pedro
whynot
New Member
New Member

--
16 Aug 2007 06:51 AM
Do you know how many rows this view return? It must be very large. If you explored every road and still cannot get a satisfied performance, then it time to take another approach, reduce the output record set and intermediate record set by creating specific views istead of such generic view.

In this view, I would try to change code to this (I only changed one "select" as a example). In case query optimizer failed to optimize your query, this will help it understand better.

SELECT CO.CODCTB AS CODCTB, CO.NIFCTB AS NIFCTB, CO.NOMECTB AS NOMECTB, CO.MORADACTB AS MORADACTB
,LEFT(CO.MORADACTB,120) AS MORADACTB_TRUNC, CO.LOCCTB AS LOCCTB
, (SELECT DES_BAIRRO FROM dbo.BAIRRO WHERE CO.CODBAIRRO = dbo.BAIRRO.COD_BAIRRO) AS COMUNA
, (SELECT IDMUNIC FROM dbo.MUNICIPIO WHERE CO.CODMUNIC = MUNICIPIO.CODMUNIC) AS MUNICIPIO
, (SELECT DESCIDADE FROM dbo.CIDADES WHERE CO.CODCIDADE = CIDADES.CODCIDADE) AS CIDADECTB,
CO.CODRF AS CODRF, CO.CODCAE AS CODCAE, CO.CODPROV AS CODPROV
, CO.STATUSCTB AS STATUSCTB, CO.CODRTRIB AS CODRTRIB,
CO.CODMUNIC AS CODMUNIC, CONVERT(DATETIME,CO.DTAICTB) AS DTAICTB
, CONVERT(DATETIME,CO.DTACCTB) AS DTACCTB, CO.CODENQUAD AS CODENQUAD,
CO.CODSECTOR AS CODSECTOR, 5 AS TIPO, CONVERT(DATETIME,NULL) AS DTACESSACAOTCO
, CONVERT(DATETIME,NULL) AS DTACESSACAOTCP, CONVERT(DATETIME,NULL) AS DTACESSACAOA,
0 AS PSERVTCO, 0 AS PSERVTCP, 0 AS PSERVA
, CONVERT(DATETIME,CD.DATAH) AS DATAINSERE
FROM dbo.CONTRIBUINTEE CO left join
(SELECT CODCTB, MIN(DATAH) FROM dbo.H_CONTRIBUINTEE group by CODCTB) CD on CO.CODCTB=CD.CODCTB
river1
New Member
New Member

--
18 Aug 2007 01:35 AM
Hi-

I have made restore of this database no a server with 4GB of memory and two processors (3400Mhz).
No body was connected to the server, and i executed the query from the query analiser of the server.
Same time to execute the query (8 sec.)
Only a part of the resurces of the server where used.(i have seen the task manager).

query :

select * from contribuintesest


best regards,

Pedro


Acceptable Use Policy
---