Optimizing LIKE OR Selects

I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example:

table Company, fields CompanyID, CompanyName

table Address, fields AddressID, AddressName

table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID

CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter:

Select c.CompanyID, c.CompanyName, a.AddressName

FROM Company c

LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID

LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID

WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%))

There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address.

Is there any way to optimize this query, or is it a problem with the base table implementation?

Any advice would be appreciated.

? Hi Brian, Do you really need to use OUTER JOINs here? For an INNER JOIN, the optimizer can sometimes pick a more efficient execution plan. It's a long shot, but sometimes performance for a query with OR can be improved by re-writing it as a UNION of two queries: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION -- Or UNION ALL, see below SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' If your data is such that you can be sure there will never be an overlap in the results of the two UNION'ed queries, then change UNION to UNION ALL to gain some more performance. If that's not possible, then you could also try how this one runs: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION ALL SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' AND c.CompanyName NOT LIKE 'TEST%' -- Assumes CompanyName is never NULL The queries above are untested. See www.aspfaq.com/5006 if you prefer a tested reply. -- Hugo Kornelis, SQL Server MVP <Brian S. Ward@.discussions.microsoft..com> schreef in bericht news:822eb526-281c-409e-80df-9a03e79d3f05@.discussions.microsoft.com... I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example: table Company, fields CompanyID, CompanyName table Address, fields AddressID, AddressName table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter: Select c.CompanyID, c.CompanyName, a.AddressName FROM Company c LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%)) There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address. Is there any way to optimize this query, or is it a problem with the base table implementation? Any advice would be appreciated.|||

Hi Hugo,

Thanks for replying to my question. I tried a couple of the things that you mentioned.

Changing the OUTER JOINS to INNER JOINS had no noticeable effect on performance. Additionally, the execution plan seemed to become more complicated.

I tried using a UNION ALL clause between 2 SQL statements setup specifically to select AddressName and CompanyName, but performance was destroyed trying that. I used the actual version of the SQL rather than the test SQL I submitted, which contains about 7 joins.

The only solution that I can think of at this time is to change the schema of the base tables, moving the AddressName and CompanyName into the associative table, therefore allowing one search field to be indexed. It would be a bit more cryptic, but would solve the problem of the LIKE OR issue ( since there would be only one LIKE statement for both checks ).

Any other ideas would be appreciated.

|||

It sounds like having a denormalized schema like you suggest may speed things up. There is nothing wrong in duplicating the addressname and company name fields in the one table, lots of companies have denormalized databases for performance purposes. I used to work on a database for one of the biggest Oil companies in the world, and that was largely denormalized and had no relationships set up (they were enforced by triggers and in the stored procedures).

An alternative which may work (although its a long shot) is to rewrite the OR as a not and such that

A OR B = NOT(NOT A AND NOT B)

One of my former colleagues used to assure me that was faster, but I have never tested it. It works as all computers are built from NAND gates, and thus any boolean statement can be rewitten as a series of NANDs

|||

Hi,

Have you tried to run it as to queries?

Without the OR statement.

Try that and see if it gets better.

If so, then insert the result into a temptable and make the final select from there.

It's hard to speed upp OR selects.

Regards

|||

I've tried that too, running 2 queries then trying to merge them after, but that becomes pretty convoluted trying to decide which records from the 2 sets makes the Top 100. I've decided to go with the denormalization plan for now, populating a 'Name' field in the associative table and using that to search. One index, no Or statement, runs really fast.

Thanks to everyone for your input.

|||

Can you post the statistics profile output (and the xml showplan, if possible)?

We can tell where things are wrong based on that.

Thanks,

Conor

sql

Optimizing LIKE OR Selects

I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example:

table Company, fields CompanyID, CompanyName

table Address, fields AddressID, AddressName

table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID

CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter:

Select c.CompanyID, c.CompanyName, a.AddressName

FROM Company c

LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID

LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID

WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%))

There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address.

Is there any way to optimize this query, or is it a problem with the base table implementation?

Any advice would be appreciated.

? Hi Brian, Do you really need to use OUTER JOINs here? For an INNER JOIN, the optimizer can sometimes pick a more efficient execution plan. It's a long shot, but sometimes performance for a query with OR can be improved by re-writing it as a UNION of two queries: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION -- Or UNION ALL, see below SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' If your data is such that you can be sure there will never be an overlap in the results of the two UNION'ed queries, then change UNION to UNION ALL to gain some more performance. If that's not possible, then you could also try how this one runs: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION ALL SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' AND c.CompanyName NOT LIKE 'TEST%' -- Assumes CompanyName is never NULL The queries above are untested. See www.aspfaq.com/5006 if you prefer a tested reply. -- Hugo Kornelis, SQL Server MVP <Brian S. Ward@.discussions.microsoft..com> schreef in bericht news:822eb526-281c-409e-80df-9a03e79d3f05@.discussions.microsoft.com... I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example: table Company, fields CompanyID, CompanyName table Address, fields AddressID, AddressName table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter: Select c.CompanyID, c.CompanyName, a.AddressName FROM Company c LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%)) There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address. Is there any way to optimize this query, or is it a problem with the base table implementation? Any advice would be appreciated.|||

Hi Hugo,

Thanks for replying to my question. I tried a couple of the things that you mentioned.

Changing the OUTER JOINS to INNER JOINS had no noticeable effect on performance. Additionally, the execution plan seemed to become more complicated.

I tried using a UNION ALL clause between 2 SQL statements setup specifically to select AddressName and CompanyName, but performance was destroyed trying that. I used the actual version of the SQL rather than the test SQL I submitted, which contains about 7 joins.

The only solution that I can think of at this time is to change the schema of the base tables, moving the AddressName and CompanyName into the associative table, therefore allowing one search field to be indexed. It would be a bit more cryptic, but would solve the problem of the LIKE OR issue ( since there would be only one LIKE statement for both checks ).

Any other ideas would be appreciated.

|||

It sounds like having a denormalized schema like you suggest may speed things up. There is nothing wrong in duplicating the addressname and company name fields in the one table, lots of companies have denormalized databases for performance purposes. I used to work on a database for one of the biggest Oil companies in the world, and that was largely denormalized and had no relationships set up (they were enforced by triggers and in the stored procedures).

An alternative which may work (although its a long shot) is to rewrite the OR as a not and such that

A OR B = NOT(NOT A AND NOT B)

One of my former colleagues used to assure me that was faster, but I have never tested it. It works as all computers are built from NAND gates, and thus any boolean statement can be rewitten as a series of NANDs

|||

Hi,

Have you tried to run it as to queries?

Without the OR statement.

Try that and see if it gets better.

If so, then insert the result into a temptable and make the final select from there.

It's hard to speed upp OR selects.

Regards

|||

I've tried that too, running 2 queries then trying to merge them after, but that becomes pretty convoluted trying to decide which records from the 2 sets makes the Top 100. I've decided to go with the denormalization plan for now, populating a 'Name' field in the associative table and using that to search. One index, no Or statement, runs really fast.

Thanks to everyone for your input.

|||

Can you post the statistics profile output (and the xml showplan, if possible)?

We can tell where things are wrong based on that.

Thanks,

Conor

Optimizing a query

Hi
I have a query which I would like to optimize and thought I had found a
solution.
The query is a join of several selects from several different tables.
So we have in general the following:
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
JOIN
SELECT table1.Col1 FROM table2 WHERE exp2
ON table1.Col1 = table2.Col1
)
or something like that. Of course my query was a lot larger than this,
but it is just to illustrate the problem.
The above was too slow, and I made an attempt to optimize it and
thought it did work. The following is what I did:
Instead of the SELECT's I could create temporary tables and select into
those so I get the following:
SELECT table1.Col1
INTO #temp_table1
FROM table1 WHERE exp1
SELECT table1.Col1
INTO #temp_table2
FROM table2 WHERE exp2
And then join those two tables:
SELECT Col1 FROM
(
#temp_table1
JOIN
#temp_table2
ON #temp_table1.Col1 = #temp_table2.Col1
)
and at the end drop the temporary tables.
DROP Table #temp_table1
DROP Table #temp_table2
This gives the same result as the original statement.
I profiled a bit and saw that if I created the temp tables, and ran the
join statement several times after each other, the first time it is run
it take a lot of time compared to the following. Can anyone explain to
me why this is the case? I thought that the temporary tables were
created and loaded into the cache, and therefore all calls to the big
JOIN statement should take equally long time.
The thing is that I cannot reuse the temporary tables, so I get no
benefit from all the above.
Thank you very much in advance
JeejiHi
First of all the first script throws a syntax error. Is that MySQL syntax?
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 JOIN table2 ON table1.Col1 =table2.Col1 WHERE exp1 AND ....
) AS Der
--Or
SELECT * FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
UNION ALL
SELECT table1.Col1 FROM table2 WHERE exp2
) AS Der
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Uri,
I think that is just a small fragment of the query. (At least I hope it's
just a fragment...)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Hi.
I thought I made it clear that it was just a fragment of the code. But
now something new came to my attention.
I can see that if I clear the SQL server cache and call the first SQL
statement (the one without the temporary table), the SQL profiler shows
that this call takes longer (around twice) than it takes if I clear the
server cache and run the temporary table version of the call.
However if I do the same calls from a C# application, the opposite
occurs.
To clear the SQL server cache I use:
DBCC FreeSystemCache('All')
And for the C# application I am using .Net 2.0, using the
System.Data.SQLClient calls.
Any advice would just be great.
Arnie Rowland wrote:
> Uri,
> I think that is just a small fragment of the query. (At least I hope it's
> just a fragment...)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "jeeji" <jihad_dk@.yahoo.com> wrote in message
> news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> > Hi
> >
> > I have a query which I would like to optimize and thought I had found a
> > solution.
> >
> > The query is a join of several selects from several different tables.
> >
> > So we have in general the following:
> >
> > SELECT Col1 FROM
> > (
> > SELECT table1.Col1 FROM table1 WHERE exp1
> > JOIN
> > SELECT table1.Col1 FROM table2 WHERE exp2
> > ON table1.Col1 = table2.Col1
> > )
> >
> > or something like that. Of course my query was a lot larger than this,
> > but it is just to illustrate the problem.
> >
> > The above was too slow, and I made an attempt to optimize it and
> > thought it did work. The following is what I did:
> > Instead of the SELECT's I could create temporary tables and select into
> > those so I get the following:
> >
> > SELECT table1.Col1
> > INTO #temp_table1
> > FROM table1 WHERE exp1
> >
> > SELECT table1.Col1
> > INTO #temp_table2
> > FROM table2 WHERE exp2
> >
> > And then join those two tables:
> >
> > SELECT Col1 FROM
> > (
> > #temp_table1
> > JOIN
> > #temp_table2
> > ON #temp_table1.Col1 = #temp_table2.Col1
> > )
> >
> > and at the end drop the temporary tables.
> >
> > DROP Table #temp_table1
> > DROP Table #temp_table2
> >
> > This gives the same result as the original statement.
> >
> > I profiled a bit and saw that if I created the temp tables, and ran the
> > join statement several times after each other, the first time it is run
> > it take a lot of time compared to the following. Can anyone explain to
> > me why this is the case? I thought that the temporary tables were
> > created and loaded into the cache, and therefore all calls to the big
> > JOIN statement should take equally long time.
> > The thing is that I cannot reuse the temporary tables, so I get no
> > benefit from all the above.
> >
> > Thank you very much in advance
> > Jeeji
> >|||Sorry for the distrubance guys. I figured it out myself.
The problem is that I did not clear the cache from the C# application.
If I did, the same results show.
Jeeji
jeeji skrev:
> Hi.
> I thought I made it clear that it was just a fragment of the code. But
> now something new came to my attention.
> I can see that if I clear the SQL server cache and call the first SQL
> statement (the one without the temporary table), the SQL profiler shows
> that this call takes longer (around twice) than it takes if I clear the
> server cache and run the temporary table version of the call.
> However if I do the same calls from a C# application, the opposite
> occurs.
> To clear the SQL server cache I use:
> DBCC FreeSystemCache('All')
> And for the C# application I am using .Net 2.0, using the
> System.Data.SQLClient calls.
> Any advice would just be great.
> Arnie Rowland wrote:
> > Uri,
> >
> > I think that is just a small fragment of the query. (At least I hope it's
> > just a fragment...)
> >
> > --
> > Arnie Rowland, YACE*
> > "To be successful, your heart must accompany your knowledge."
> >
> > *Yet Another Certification Exam
> >
> >
> > "jeeji" <jihad_dk@.yahoo.com> wrote in message
> > news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> > > Hi
> > >
> > > I have a query which I would like to optimize and thought I had found a
> > > solution.
> > >
> > > The query is a join of several selects from several different tables.
> > >
> > > So we have in general the following:
> > >
> > > SELECT Col1 FROM
> > > (
> > > SELECT table1.Col1 FROM table1 WHERE exp1
> > > JOIN
> > > SELECT table1.Col1 FROM table2 WHERE exp2
> > > ON table1.Col1 = table2.Col1
> > > )
> > >
> > > or something like that. Of course my query was a lot larger than this,
> > > but it is just to illustrate the problem.
> > >
> > > The above was too slow, and I made an attempt to optimize it and
> > > thought it did work. The following is what I did:
> > > Instead of the SELECT's I could create temporary tables and select into
> > > those so I get the following:
> > >
> > > SELECT table1.Col1
> > > INTO #temp_table1
> > > FROM table1 WHERE exp1
> > >
> > > SELECT table1.Col1
> > > INTO #temp_table2
> > > FROM table2 WHERE exp2
> > >
> > > And then join those two tables:
> > >
> > > SELECT Col1 FROM
> > > (
> > > #temp_table1
> > > JOIN
> > > #temp_table2
> > > ON #temp_table1.Col1 = #temp_table2.Col1
> > > )
> > >
> > > and at the end drop the temporary tables.
> > >
> > > DROP Table #temp_table1
> > > DROP Table #temp_table2
> > >
> > > This gives the same result as the original statement.
> > >
> > > I profiled a bit and saw that if I created the temp tables, and ran the
> > > join statement several times after each other, the first time it is run
> > > it take a lot of time compared to the following. Can anyone explain to
> > > me why this is the case? I thought that the temporary tables were
> > > created and loaded into the cache, and therefore all calls to the big
> > > JOIN statement should take equally long time.
> > > The thing is that I cannot reuse the temporary tables, so I get no
> > > benefit from all the above.
> > >
> > > Thank you very much in advance
> > > Jeeji
> > >sql

Optimising Query based on Views

I would be grateful for some advice.
I have a query which selects from a view which, in turn, is based on three
other view. I want to optimise the query.
The query is built dynamically so cannot be made into a stored procedure. Is
it worth my while making the main view into a user-defined function so that
I can select from it. (I understand that you can't do "SELECT * FROM
SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC GROUP
BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
user-defined function.) I am suggesting this because I have the impression
that stored procedures and user-defined functions are pre-compiled with an
execution plan by SQL server, whereas this is not possible for views.
Many thanks in advance,
Richard Cox.Yes and no. Views are totally transparent to the optimizer and are only
useful as an abstraction layer and security feature.. The optimizer looks
at the underlying tables rather than the view, EXCEPT for partitioned views
which I will conveniently ignore here.
The big advantage to a stored procedure is query plan reuse. The optimizer
figures out its 'best' plan once and reuses it until it is no longer valid
or it is aged out of cache. If you call the procedure once a day, this
won't help much. On the other hand, even stored procedures have limits.
Temporary tables and dynamic SQL are two of the biggest reasons why a stored
procedure will be recompiled.
From your description, the result set and filter conditions may change from
execution to execution so the advantage of plan reuse just doesn't apply.
You may have to construct a few samples and see if the optimizer does what
you think it should. Use the 'View Estimated Execution Plan' button in
Query Analyzer to see what SQL will do with various combinations of your
query.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:en6D1q54DHA.2736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.

---

Is

quote:

---

> it worth my while making the main view into a user-defined function so

---

that

quote:

---

> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC

---

GROUP

quote:

---

> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>

---

|||Thanks very much for your explanation, Geoff. Looks like there is nothing
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>

---

Optimising Query based on Views

I would be grateful for some advice.
I have a query which selects from a view which, in turn, is based on three
other view. I want to optimise the query.
The query is built dynamically so cannot be made into a stored procedure. Is
it worth my while making the main view into a user-defined function so that
I can select from it. (I understand that you can't do "SELECT * FROM
SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC GROUP
BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
user-defined function.) I am suggesting this because I have the impression
that stored procedures and user-defined functions are pre-compiled with an
execution plan by SQL server, whereas this is not possible for views.
Many thanks in advance,
Richard Cox.Yes and no. Views are totally transparent to the optimizer and are only
useful as an abstraction layer and security feature.. The optimizer looks
at the underlying tables rather than the view, EXCEPT for partitioned views
which I will conveniently ignore here.
The big advantage to a stored procedure is query plan reuse. The optimizer
figures out its 'best' plan once and reuses it until it is no longer valid
or it is aged out of cache. If you call the procedure once a day, this
won't help much. On the other hand, even stored procedures have limits.
Temporary tables and dynamic SQL are two of the biggest reasons why a stored
procedure will be recompiled.
From your description, the result set and filter conditions may change from
execution to execution so the advantage of plan reuse just doesn't apply.
You may have to construct a few samples and see if the optimizer does what
you think it should. Use the 'View Estimated Execution Plan' button in
Query Analyzer to see what SQL will do with various combinations of your
query.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:en6D1q54DHA.2736@.TK2MSFTNGP09.phx.gbl...
> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.
Is
> it worth my while making the main view into a user-defined function so
that
> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC
GROUP
> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>|||Thanks very much for your explanation, Geoff. Looks like there is nothing
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...
> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>

Optimalization - 3 SELECTs with one condition OR one SELECT with three conditions

Hello everyone.
I have got a simple question, but very important for me.
I making a quite big report and I have a lot of conditions to compare.
And I wonder, what is faster:
One SELECT statement with three comparing conditions
OR
Three SELECTS, each with one comparing condition
All the conditions are separated by AND condition.
The Store Procedure is being made on MS SQL SERVER 2000, the size of
table is around 22098165 records.
Thanks a lot for help... marianowic
to try a believable answer, i would like to have some example:
could you post the tables design (just the create script), an example of
your queries and some information about indexes on that tables?
Gilberto Zampatti
"marianowic" wrote:

> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>
|||> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
How will 3 independent SELECT statements to provide the desired results with
AND conditions? I would expect the single statement approach to be best.
Performance largely depends on available indexes.
Hope this helps.
Dan Guzman
SQL Server MVP
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegro ups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>
|||If you (due to lack of indexes or the nature of the queries) have to read in
the full table for any of the three, then I would bet it is more efficient
to combine everything into one query since it is ALWAYS more efficient to
read data once than multiple times. I/O is the biggest reason for
performance issues.
However, if your 3 queries each use different indexes and hit different
sections of the tables, then a multi-statement approach may be best.
I will agree with other posters that we can't help you very much with such
limited information.
TheSQLGuru
President
Indicium Resources, Inc.
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegro ups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>
|||> I have got a simple question, but very important for me.
ok

> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
If you have 3 selects, do you use them all in one report? If yes, then
it does not matter if you use 1 or 3 selects, you will end up with lot
of records.

Optimalization - 3 SELECTs with one condition OR one SELECT with three conditions

Hello everyone.
I have got a simple question, but very important for me.
I making a quite big report and I have a lot of conditions to compare.
And I wonder, what is faster:
One SELECT statement with three comparing conditions
OR
Three SELECTS, each with one comparing condition
All the conditions are separated by AND condition.
The Store Procedure is being made on MS SQL SERVER 2000, the size of
table is around 22098165 records.
Thanks a lot for help... marianowicto try a believable answer, i would like to have some example:
could you post the tables design (just the create script), an example of
your queries and some information about indexes on that tables?
Gilberto Zampatti
"marianowic" wrote:
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
How will 3 independent SELECT statements to provide the desired results with
AND conditions? I would expect the single statement approach to be best.
Performance largely depends on available indexes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegroups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||If you (due to lack of indexes or the nature of the queries) have to read in
the full table for any of the three, then I would bet it is more efficient
to combine everything into one query since it is ALWAYS more efficient to
read data once than multiple times. I/O is the biggest reason for
performance issues.
However, if your 3 queries each use different indexes and hit different
sections of the tables, then a multi-statement approach may be best.
I will agree with other posters that we can't help you very much with such
limited information.
--
TheSQLGuru
President
Indicium Resources, Inc.
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegroups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||> I have got a simple question, but very important for me.
ok
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
If you have 3 selects, do you use them all in one report? If yes, then
it does not matter if you use 1 or 3 selects, you will end up with lot
of records.

Optimalization - 3 SELECTs with one condition OR one SELECT with three conditions

Hello everyone.
I have got a simple question, but very important for me.
I making a quite big report and I have a lot of conditions to compare.
And I wonder, what is faster:
One SELECT statement with three comparing conditions
OR
Three SELECTS, each with one comparing condition
All the conditions are separated by AND condition.
The Store Procedure is being made on MS SQL SERVER 2000, the size of
table is around 22098165 records.
Thanks a lot for help... marianowic> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
How will 3 independent SELECT statements to provide the desired results with
AND conditions? I would expect the single statement approach to be best.
Performance largely depends on available indexes.
Hope this helps.
Dan Guzman
SQL Server MVP
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegroups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||If you (due to lack of indexes or the nature of the queries) have to read in
the full table for any of the three, then I would bet it is more efficient
to combine everything into one query since it is ALWAYS more efficient to
read data once than multiple times. I/O is the biggest reason for
performance issues.
However, if your 3 queries each use different indexes and hit different
sections of the tables, then a multi-statement approach may be best.
I will agree with other posters that we can't help you very much with such
limited information.
TheSQLGuru
President
Indicium Resources, Inc.
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegroups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||> I have got a simple question, but very important for me.
ok

> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
If you have 3 selects, do you use them all in one report? If yes, then
it does not matter if you use 1 or 3 selects, you will end up with lot
of records.