Monday, March 26, 2012
optimized indexing
I would like to consider optimized indexing on the table which really does
not exist in my database, but will serve as an example for creating indexes.
Suppose that there is a table with following fields:
- family name,
- first name,
- dateofbirth,
- street,
- home number,
- city
Suppose also that there is an searching engine that is querying our example
table with statistics by these parameters:
- family name and first name - 50%,
- family name only - 20%,
- first name only - 15%,
- family name and street - 15%
Is it Ok (because of the best querying) to create following indexes:
- IX1 (family name, first name),
- IX2 (first name)
- IX3 (family name, street)
or there are other options?
Best Regards from SinclairSinclair (Sinclair@.discussions.microsoft.com) writes:
> I would like to consider optimized indexing on the table which really
> does not exist in my database, but will serve as an example for creating
> indexes.
> Suppose that there is a table with following fields:
> - family name,
> - first name,
> - dateofbirth,
> - street,
> - home number,
> - city
> Suppose also that there is an searching engine that is querying our
> example table with statistics by these parameters:
> - family name and first name - 50%,
> - family name only - 20%,
> - first name only - 15%,
> - family name and street - 15%
> Is it Ok (because of the best querying) to create following indexes:
> - IX1 (family name, first name),
> - IX2 (first name)
> - IX3 (family name, street)
> or there are other options?
Well, the above is not bad, but it fails to take in account the question is
which index to make the clustered index.
Since family name is popular, that is my pick for the clustered index. This
results in:
ClusterIX - (family naee)
NC_IX1 - (first name)
NC_IX2 - (street)
You may be missing family name from the non-clustered indexes, but it is
not. The clustrered index works as row locator in the non-clustered indexes,
so all columns in the clustered index appears there as well. (It would not
be wrong to explicitly include family name in these indexes.)
> I apologize to ask again. Related to the example in previous question
> about optimized indexing, should there be changes in proposed indexes,
> if the field [family name] takes part in INNER JOIN with some other
> table (Foreign key)?
With knowing only this, it is difficult to say whether it would matter
at all. But if it does, it is likely to enfoce the idea that family
name should be the clustered index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Optimize the Query
To check to see if all the values exist in the following list: '806478',
'806479','806480','806481' in the Call_Movements table.
I am only interest in the first six characters and I'm converting iNum data
type from money to varchar(20).
left(cast(iNum as varchar(20)),6)
Please help me optimize the t-sql statement listed below since this table is
large.
Is there away to change the following to binary format:
DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
listed below to be optimal?
Thank You,
T-SQL
DECLARE @.CNT_MTN_REC_0 SMALLINT
SET @.CNT_MTN_REC_0 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806478'))
--
-- Count the
--
DECLARE @.CNT_MTN_REC_1 SMALLINT
SET @.CNT_MTN_REC_1 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806479'))
--
--
--
DECLARE @.CNT_MTN_REC_2 SMALLINT
SET @.CNT_MTN_REC_2 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806480'))
--
--
--
DECLARE @.CNT_MTN_REC_3 SMALLINT
SET @.CNT_MTN_REC_3 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806481'))
--
--
--
if (@.CNT_MTN_REC_0 = 0) OR (@.CNT_MTN_REC_1 = 0) OR (@.CNT_MTN_REC_2 = 0) OR
(@.CNT_MTN_REC_3 = 0)
BEGIN
PRINT "Error has Occurred'
ENDJoe K. (Joe K.@.discussions.microsoft.com) writes:
> To check to see if all the values exist in the following list: '806478',
> '806479','806480','806481' in the Call_Movements table.
> I am only interest in the first six characters and I'm converting iNum
> data type from money to varchar(20).
> left(cast(iNum as varchar(20)),6)
> Please help me optimize the t-sql statement listed below since this
> table is large.
> Is there away to change the following to binary format:
> DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
> listed below to be optimal?
It would have helped if you had posted the CREATE TABLE and CREATE INDEX
statements for the tables.
But first, there is no need to run four SQL statemennts.
This could either be done as:
Select count(iNum), left(cast(iNum as varchar(20)),6)
from Call_Movements
where StartTime >= DATEADD(mi, -60, GETDATE())
AND left(cast(iNum as varchar(20)),6) IN
('806478', '806479', '806480', '806481')
GROUP BY left(cast(iNum as varchar(20)),6)
This produces a result set of four rows. If you need to get the result
into variables, you can do:
Select @.CNT_MTN_REC_0 = SUM(CASE left(cast(iNum as varchar(20)),6)
WHEN '806478' THEN 1
ELSE 0
END),
..
from Call_Movements
where StartTime >= DATEADD(mi, -60, GETDATE())
AND left(cast(iNum as varchar(20)),6) IN
('806478', '806479', '806480', '806481')
As you can see, I have also changed the condition on Start_Time, in case
this column is indexed. When an indexed column appears in an expression
like in your query, the index is of on use. The query is still problematic
due to the >=. If the index on StartTime is clustered it is not much of
an issue, but if there is only a onn-clustered index, the optimizer is not
likely to pick it in this case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Joe,
Will you have iNum values that start with 806478 (or any of the other 3
values) followed by other numbers before the decimal point? For example
8064781.01?
If not, then make sure you add a nonclustered index on
Call_Movement(iNum, StartTime) and add the following predicates to the
WHERE clause:
AND iNum >= 806478
AND iNum < 806482
You could try the following query:
IF ( SELECT COUNT(DISTINCT CAST(iNum as char(6)) )
FROM Call_Movements
WHERE StartTime >= DATEADD(minute, -60, CURRENT_TIMESTAMP)
AND iNum >= CAST(806478 AS money)
AND iNum < CAST(806482 AS money)
AND CAST(iNum as char(6)) IN ('806478', '806479', '806480',
'806481')
) < 4
BEGIN
PRINT "Error has Occurred'
END
HTH,
Gert-Jan
Joe K. wrote:
> Listed below is tsql statement that I set up execute every 60 minutes.
> To check to see if all the values exist in the following list: '806478',
> '806479','806480','806481' in the Call_Movements table.
> I am only interest in the first six characters and I'm converting iNum dat
a
> type from money to varchar(20).
> left(cast(iNum as varchar(20)),6)
> Please help me optimize the t-sql statement listed below since this table
is
> large.
> Is there away to change the following to binary format:
> DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
> listed below to be optimal?
> Thank You,
>
> T-SQL
> DECLARE @.CNT_MTN_REC_0 SMALLINT
> SET @.CNT_MTN_REC_0 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806478'))
> --
> -- Count the
> --
> DECLARE @.CNT_MTN_REC_1 SMALLINT
> SET @.CNT_MTN_REC_1 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806479'))
> --
> --
> --
> DECLARE @.CNT_MTN_REC_2 SMALLINT
> SET @.CNT_MTN_REC_2 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806480'))
> --
> --
> --
> DECLARE @.CNT_MTN_REC_3 SMALLINT
> SET @.CNT_MTN_REC_3 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806481'))
> --
> --
> --
> if (@.CNT_MTN_REC_0 = 0) OR (@.CNT_MTN_REC_1 = 0) OR (@.CNT_MTN_REC_2 = 0) OR
> (@.CNT_MTN_REC_3 = 0)
> BEGIN
> PRINT "Error has Occurred'
> END|||And encapsulate LEFT operation to sub-query may be get more good
performance.
Gert-Jan Strik =E5=86=99=E9=81=93=EF=BC=9A
> Joe,
> Will you have iNum values that start with 806478 (or any of the other 3
> values) followed by other numbers before the decimal point? For example
> 8064781.01?
> If not, then make sure you add a nonclustered index on
> Call_Movement(iNum, StartTime) and add the following predicates to the
> WHERE clause:
> AND iNum >=3D 806478
> AND iNum < 806482
> You could try the following query:
> IF ( SELECT COUNT(DISTINCT CAST(iNum as char(6)) )
> FROM Call_Movements
> WHERE StartTime >=3D DATEADD(minute, -60, CURRENT_TIMESTAMP)
> AND iNum >=3D CAST(806478 AS money)
> AND iNum < CAST(806482 AS money)
> AND CAST(iNum as char(6)) IN ('806478', '806479', '806480',
> '806481')
> ) < 4
> BEGIN
> PRINT "Error has Occurred'
> END
>
> HTH,
> Gert-Jan
>
> Joe K. wrote:
data
le is
=3D 0) OR|||"navyzhu@.gmail.com" wrote:
> And encapsulate LEFT operation to sub-query may be get more good
> performance.
I have not done performance tests to disprove it, but I highly doubt it!
I don't think LEFT will outperform CAST.
Besides, why use the proprietary LEFT when the standard CAST will do
just fine...
Gert-Jan
> Gert-Jan Strik 写道:
>
Saturday, February 25, 2012
Operating system error 1450(Insufficient system resources exist to complete the requested se
Hello!
Hopefully someone can help me.
I have scripts to refresh database as SQL daily jobs. (O.S is Win2K3 and SQL server 2000 and SP4) It was worked and I got the following message this morning from SQL error log.
Internal I/O request 0x5FDA3C50: Op: Read, pBuffer: 0x0D860000, Size: 65536, Position: 25534864896, RetryCount: 10, UMS: Internal: 0x483099C8, InternalHigh: 0x0, Offset: 0xF1FF1E00, OffsetHigh: 0x5, m_buf: 0x0D860000, m_len: 65536, m_actualBytes: 0, m_errcode: 1450, BackupFile: \\XAPROD12MASTER\XAPROD\XAPROD_db_200701290000.BAK
BackupMedium::ReportIoError: read failure on backup device '\\XAPROD12MASTER\XAPROD\XAPROD_db_200701290000.BAK'. Operating system error 1450(Insufficient system resources exist to complete the requested service.).
This sounds exactly like an OS bug that we saw a long time ago where the Windows Remote File cache was behaving poorly and running out of its own buffers.
The best approach is to take this to Windows and see if there's a patch.