optimize queries.

how to optimize the following queries.
1) SELECT * FROM employe
WHERE job_id not IN ( SELECT job_i
FROM job
WHERE min_lvl = 25
2) select * from tbla where name like '%F
Regard
sanjaysanjay
It is to hard to say . You did not provide any info about how many data
exists in the table?
How often you query the table?
What is indexes on the table?
My guess.
1) SELECT * FROM employee
WHERE NOT EXISTS
( SELECT jobs .job_id
FROM jobs WHERE employee.job_id
=jobs.job_id AND min_lvl = 25 )
> 2) select * from tbla where name like '%F'
This one may prevent query optimyzer for using an index on the name column
unless you re-write it as 'F%'.
Consider to use covering indexes instead of select * from table .
For more details please refer to BOL
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:19EA7547-8EBC-43FF-9981-3AF635B34A03@.microsoft.com...
> how to optimize the following queries..
> 1) SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
> 2) select * from tbla where name like '%F'
> Regards
> sanjay|||h
im using not In in my query..which as per documents/sqlserver etc..is not the best way to use and it hampers performance...how i can avoide not In.
execution plan of Not In/ Not exists are same.
suppost i ve 1 million records in this tables and its being heavily used.
both are normal tables from pUbs database.
=========2) if we rewrite the query as 'F%' this will change the logic of the query..keeping same logic how can we modify the query|||sanjay
1) Look , another approach but you will have to compare all methods .
1) SELECT * FROM employee E LEFT JOIN jobs J
ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
2)
http://www.sql-server-performance.com/covering_indexes.asp
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:AFD7096E-56CF-4C1C-B71D-F4862A8EA60D@.microsoft.com...
> hi
> im using not In in my query..which as per documents/sqlserver etc..is not
the best way to use and it hampers performance...how i can avoide not In..
> execution plan of Not In/ Not exists are same..
> suppost i ve 1 million records in this tables and its being heavily used..
> both are normal tables from pUbs database..
> ==========> 2) if we rewrite the query as 'F%' this will change the logic of the
query..keeping same logic how can we modify the query.
>|||hi
i tried ..but seems some problem.
SELECT * FROM employee
WHERE job_id not IN ( SELECT job_id
FROM jobs
WHERE min_lvl = 25 )
the above query returned 34 rows...whereas the below query only 9 rows...i think its using left join bcoz of that..
SELECT * FROM employee E LEFT JOIN jobs J
ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
any further updates would be highly appreciated..
regards|||sanjay
Can post DDL + sample data + expected result and then I can answer you why
it returns 9 rows and why it returns 34 rows.
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:60500162-DCCE-4D7A-9B3F-BE4A3C50B05C@.microsoft.com...
> hi
> i tried ..but seems some problem.
> SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
> the above query returned 34 rows...whereas the below query only 9 rows...i
think its using left join bcoz of that..
> SELECT * FROM employee E LEFT JOIN jobs J
> ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
> any further updates would be highly appreciated..
> regards
>|||Try below query :
select e.* from employee e, ( SELECT job_id
FROM jobs
WHERE min_lvl <> 25 ) a
where e.job_id = a.job_id
This would increase performance, as for each of the job_id, it wont scan
jobs table.
- Ketan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OFu0osW6DHA.2460@.TK2MSFTNGP09.phx.gbl...
> sanjay
> Can post DDL + sample data + expected result and then I can answer you
why
> it returns 9 rows and why it returns 34 rows.
>
>
> "sanjay" <anonymous@.discussions.microsoft.com> wrote in message
> news:60500162-DCCE-4D7A-9B3F-BE4A3C50B05C@.microsoft.com...
> > hi
> > i tried ..but seems some problem.
> > SELECT * FROM employee
> > WHERE job_id not IN ( SELECT job_id
> > FROM jobs
> > WHERE min_lvl = 25 )
> >
> > the above query returned 34 rows...whereas the below query only 9
rows...i
> think its using left join bcoz of that..
> >
> > SELECT * FROM employee E LEFT JOIN jobs J
> > ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
> >
> > any further updates would be highly appreciated..
> > regards
> >
>|||sanjay wrote:
> how to optimize the following queries..
> 1) SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
1) keep it as it is
2) use EXISTS
SELECT *
FROM Employee
WHERE NOT EXISTS (
SELECT 1
FROM Jobs
WHERE Jobs.job_id = Employee.job_id
AND min_lvl=25
)
3) use OUTER JOIN
SELECT * -- Add DISTINCT if necessary
FROM Employee
LEFT JOIN Jobs
ON Jobs.job_id = Employee.job_id
AND Jobs.min_lvl = 25
> 2) select * from tbla where name like '%F'
ALTER TABLE tbla
ADD NameReverse varchar(8000) not null -- use same definition as column
Name
go
UPDATE tbla
SET NameReverse = REVERSE(Name)
go
CREATE INDEX IX_tbla_NameReverse ON tbla(NameReverse)
go
SELECT *
FROM tbla
WHERE NameReverse like REVERSE('F')+'%'
Hope this helps,
Gert-Jan|||Submitted too fast. Of course, the line "WHERE Jobs.job_id IS NULL"
needs to be added to solution 3...
Gert-Jan
Gert-Jan Strik wrote:
> sanjay wrote:
> >
> > how to optimize the following queries..
> > 1) SELECT * FROM employee
> > WHERE job_id not IN ( SELECT job_id
> > FROM jobs
> > WHERE min_lvl = 25 )
> 1) keep it as it is
> 2) use EXISTS
> SELECT *
> FROM Employee
> WHERE NOT EXISTS (
> SELECT 1
> FROM Jobs
> WHERE Jobs.job_id = Employee.job_id
> AND min_lvl=25
> )
> 3) use OUTER JOIN
> SELECT * -- Add DISTINCT if necessary
> FROM Employee
> LEFT JOIN Jobs
> ON Jobs.job_id = Employee.job_id
> AND Jobs.min_lvl = 25
> > 2) select * from tbla where name like '%F'
> ALTER TABLE tbla
> ADD NameReverse varchar(8000) not null -- use same definition as column
> Name
> go
> UPDATE tbla
> SET NameReverse = REVERSE(Name)
> go
> CREATE INDEX IX_tbla_NameReverse ON tbla(NameReverse)
> go
> SELECT *
> FROM tbla
> WHERE NameReverse like REVERSE('F')+'%'
> Hope this helps,
> Gert-Jansql