Pb table scan

I have a query like this :
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn
04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
choice[vbcol=seagreen]
traceon,[vbcol=seagreen]
understand[vbcol=seagreen]
>

Pb table scan

I have a query like this :
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a écrit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
> > I have a query like this :
> >
> > select * from table
> > where champs1=val1 and champs2 =val2
> > I have an index on (champs1,champs2).
> > The table has 698694 rows.
> > The table scan cost 17770 i/os.
> >
> > If the number of rows selected is greater than 4700, the optimizer
choice
> > the table scan. If i force the index, i have 4762 i/os ( is less than
> > 17770 )
> >
> > I don't know why the optimizer choice the table scan ( is there a
traceon,
> > command like the dbcc traceon(302) in sybase, that help you to
understand
> > the choice of the optimizer ? ).
> >
> > Thanks and sorry for my poor english.
> >
> >
> >
> >
> >
> >
>

Pb table scan

I have a query like this :
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.
Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
choice[vbcol=seagreen]
traceon,[vbcol=seagreen]
understand
>
sql

PATINDEX, LIKE and escaping

Hi,
I need to scan some character data for the presence of certain "invalid"
characters. The characters I need to scan for are
!@.#$%^&*()_+={}[]|\:;"<>,/?
The PATINDEX function works so long as the ] character is omitted, e.g.
set @.s = 'foo$'
select patindex('%[!@.#$%^&*()_+={}[|\:;"<>,/?]%', @.s)
-- returns 4
Is there any way to tell PATINDEX or LIKE to treat ] as a literal character
within a character set?
Thanks,
DanielDECLARE @.s VARCHAR(20)
SET @.s = 'foo]bar['
SELECT PATINDEX( '%]%', @.s ), PATINDEX( '%[[]%', @.s )
Anith|||Surround the OPENING bracket with brackets.
set @.s = 'foo$'
select patindex('%[!@.#$%^&*()_+={}[[]|\:;"<>,/?]%', @.s)
-- returns 0
"Daniel Pratt" <kolREMOVETHISkata_is@.hotmail.com> wrote in message
news:usIkVir6FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I need to scan some character data for the presence of certain "invalid"
> characters. The characters I need to scan for are
> !@.#$%^&*()_+={}[]|\:;"<>,/?
> The PATINDEX function works so long as the ] character is omitted, e.g.
> set @.s = 'foo$'
> select patindex('%[!@.#$%^&*()_+={}[|\:;"<>,/?]%', @.s)
> -- returns 4
> Is there any way to tell PATINDEX or LIKE to treat ] as a literal
> character within a character set?
> Thanks,
> Daniel
>