I am wondering: I have a database with a few disjoint sets, and I was wondering what people's thoughts and reccomendations are:
In general, does it make more sense to make multiple queries on a database, or use large joins (in my case, outer joins)?
Is there different scenarios where one approach is better than the other?
I have an environment which has both (initially) small to mid - sized data size and rather light accesses to my app. Any suggestions?
Thanks!
Andrew Wied
It really depends on way too many factors... how much data is coming from
each side of the join? how many rows are in each table? how effective are
the on and where clauses in limiting results based on an index (or a scan)?
Note that when you run a query against 10,000 rows and then a query against
100,000 rows, your physical I/O will be as much as 110,000. However if you
join these two tables, depending on the above and other factors, your I/O
could be 10,000 * 100,000 (there may be an operation for every row in one
table against every row in the other).
Also remember that if you are using a client to display these results, it
may also be better to stagger the output instead of giving them one big
chunk of data all at once.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Wied" <anonymous@.discussions.microsoft.com> wrote in message
news:4F8067E3-E4C3-4A24-A7E9-705295107956@.microsoft.com...
> I am wondering: I have a database with a few disjoint sets, and I was
wondering what people's thoughts and reccomendations are:
> In general, does it make more sense to make multiple queries on a
database, or use large joins (in my case, outer joins)?
> Is there different scenarios where one approach is better than the
other?
> I have an environment which has both (initially) small to mid - sized data
size and rather light accesses to my app. Any suggestions?
> Thanks!
> Andrew Wied
Showing posts with label joins. Show all posts
Showing posts with label joins. Show all posts
Monday, March 26, 2012
General Advice: Multiple queries VS large joins
I am wondering: I have a database with a few disjoint sets, and I was wondering what people's thoughts and reccomendations are
In general, does it make more sense to make multiple queries on a database, or use large joins (in my case, outer joins)
Is there different scenarios where one approach is better than the other
I have an environment which has both (initially) small to mid - sized data size and rather light accesses to my app. Any suggestions?
Thanks
Andrew WiedIt really depends on way too many factors... how much data is coming from
each side of the join? how many rows are in each table? how effective are
the on and where clauses in limiting results based on an index (or a scan)?
Note that when you run a query against 10,000 rows and then a query against
100,000 rows, your physical I/O will be as much as 110,000. However if you
join these two tables, depending on the above and other factors, your I/O
could be 10,000 * 100,000 (there may be an operation for every row in one
table against every row in the other).
Also remember that if you are using a client to display these results, it
may also be better to stagger the output instead of giving them one big
chunk of data all at once.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Wied" <anonymous@.discussions.microsoft.com> wrote in message
news:4F8067E3-E4C3-4A24-A7E9-705295107956@.microsoft.com...
> I am wondering: I have a database with a few disjoint sets, and I was
wondering what people's thoughts and reccomendations are:
> In general, does it make more sense to make multiple queries on a
database, or use large joins (in my case, outer joins)?
> Is there different scenarios where one approach is better than the
other?
> I have an environment which has both (initially) small to mid - sized data
size and rather light accesses to my app. Any suggestions?
> Thanks!
> Andrew Wied
In general, does it make more sense to make multiple queries on a database, or use large joins (in my case, outer joins)
Is there different scenarios where one approach is better than the other
I have an environment which has both (initially) small to mid - sized data size and rather light accesses to my app. Any suggestions?
Thanks
Andrew WiedIt really depends on way too many factors... how much data is coming from
each side of the join? how many rows are in each table? how effective are
the on and where clauses in limiting results based on an index (or a scan)?
Note that when you run a query against 10,000 rows and then a query against
100,000 rows, your physical I/O will be as much as 110,000. However if you
join these two tables, depending on the above and other factors, your I/O
could be 10,000 * 100,000 (there may be an operation for every row in one
table against every row in the other).
Also remember that if you are using a client to display these results, it
may also be better to stagger the output instead of giving them one big
chunk of data all at once.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Wied" <anonymous@.discussions.microsoft.com> wrote in message
news:4F8067E3-E4C3-4A24-A7E9-705295107956@.microsoft.com...
> I am wondering: I have a database with a few disjoint sets, and I was
wondering what people's thoughts and reccomendations are:
> In general, does it make more sense to make multiple queries on a
database, or use large joins (in my case, outer joins)?
> Is there different scenarios where one approach is better than the
other?
> I have an environment which has both (initially) small to mid - sized data
size and rather light accesses to my app. Any suggestions?
> Thanks!
> Andrew Wied
General Advice: Multiple queries VS large joins
I am wondering: I have a database with a few disjoint sets, and I was wonder
ing what people's thoughts and reccomendations are:
In general, does it make more sense to make multiple queries on a database,
or use large joins (in my case, outer joins)?
Is there different scenarios where one approach is better than the other?
I have an environment which has both (initially) small to mid - sized data s
ize and rather light accesses to my app. Any suggestions?
Thanks!
Andrew WiedIt really depends on way too many factors... how much data is coming from
each side of the join? how many rows are in each table? how effective are
the on and where clauses in limiting results based on an index (or a scan)?
Note that when you run a query against 10,000 rows and then a query against
100,000 rows, your physical I/O will be as much as 110,000. However if you
join these two tables, depending on the above and other factors, your I/O
could be 10,000 * 100,000 (there may be an operation for every row in one
table against every row in the other).
Also remember that if you are using a client to display these results, it
may also be better to stagger the output instead of giving them one big
chunk of data all at once.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Wied" <anonymous@.discussions.microsoft.com> wrote in message
news:4F8067E3-E4C3-4A24-A7E9-705295107956@.microsoft.com...
> I am wondering: I have a database with a few disjoint sets, and I was
wondering what people's thoughts and reccomendations are:
> In general, does it make more sense to make multiple queries on a
database, or use large joins (in my case, outer joins)?
> Is there different scenarios where one approach is better than the
other?
> I have an environment which has both (initially) small to mid - sized data
size and rather light accesses to my app. Any suggestions?
> Thanks!
> Andrew Wied
ing what people's thoughts and reccomendations are:
In general, does it make more sense to make multiple queries on a database,
or use large joins (in my case, outer joins)?
Is there different scenarios where one approach is better than the other?
I have an environment which has both (initially) small to mid - sized data s
ize and rather light accesses to my app. Any suggestions?
Thanks!
Andrew WiedIt really depends on way too many factors... how much data is coming from
each side of the join? how many rows are in each table? how effective are
the on and where clauses in limiting results based on an index (or a scan)?
Note that when you run a query against 10,000 rows and then a query against
100,000 rows, your physical I/O will be as much as 110,000. However if you
join these two tables, depending on the above and other factors, your I/O
could be 10,000 * 100,000 (there may be an operation for every row in one
table against every row in the other).
Also remember that if you are using a client to display these results, it
may also be better to stagger the output instead of giving them one big
chunk of data all at once.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Wied" <anonymous@.discussions.microsoft.com> wrote in message
news:4F8067E3-E4C3-4A24-A7E9-705295107956@.microsoft.com...
> I am wondering: I have a database with a few disjoint sets, and I was
wondering what people's thoughts and reccomendations are:
> In general, does it make more sense to make multiple queries on a
database, or use large joins (in my case, outer joins)?
> Is there different scenarios where one approach is better than the
other?
> I have an environment which has both (initially) small to mid - sized data
size and rather light accesses to my app. Any suggestions?
> Thanks!
> Andrew Wied
Wednesday, March 7, 2012
Function doesn't use indexes
Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
Tom
It would help a lot if we could see this "function". Is it really a
function or a stored procedure?
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>
|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
Tom
It would help a lot if we could see this "function". Is it really a
function or a stored procedure?
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>
|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>
Function doesn't use indexes
Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
TomIt would help a lot if we could see this "function". Is it really a
function or a stored procedure?
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement
.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
TomIt would help a lot if we could see this "function". Is it really a
function or a stored procedure?
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement
.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>
Function doesn't use indexes
Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
TomIt would help a lot if we could see this "function". Is it really a
function or a stored procedure?
--
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
TomIt would help a lot if we could see this "function". Is it really a
function or a stored procedure?
--
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>
Sunday, February 26, 2012
Full-text searches
BASIC QUESTION here:
Can you do JOINS with tables in a catalog?What catalog? I'm not sure I understand your question. Do you mean the SQL Server System tables? In that case, yes you can.|||I have 'enabled' full text search capability for 3 tables in my SQL Server database and defined which columns I want indexed.
Of those tables I have enabled, can I JOIN them in queries?|||You can join them in queries regardless of whether they are indexed.|||Ok, thanks for the info.
Subscribe to:
Posts (Atom)