Fetch data from Multiple SQL Servers

Hi,
I have 10+ SQL Servers, from my head office server i want
to connect to all branch server and execute a sql
statement and results to be inserted into a table of Head
Office Server.
RegardsYou can use a linked server... ( See linked Servers in Books on line.) Set
up a linked server then use the 4 part name
insert into mytable select * from server1.pubs.dbo.titles
etc
Or you could use replication to move the data on a regular basis...
--
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
"Asmath" <anonymous@.discussions.microsoft.com> wrote in message
news:1071d01c3f3c4$e43bcdd0$a001280a@.phx.gbl...
> Hi,
> I have 10+ SQL Servers, from my head office server i want
> to connect to all branch server and execute a sql
> statement and results to be inserted into a table of Head
> Office Server.
>
> Regards|||Hi,
In my head office server, i found the list of all branch
server in Security->Remote Servers.
I found no entries in Security->LinkedServers, when i try
to add linked server, error appears already exists?
And how to use authentication process?
>--Original Message--
>You can use a linked server... ( See linked Servers in
Books on line.) Set
>up a linked server then use the 4 part name
>insert into mytable select * from server1.pubs.dbo.titles
>etc
>Or you could use replication to move the data on a
regular basis...
>--
>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
>
>"Asmath" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1071d01c3f3c4$e43bcdd0$a001280a@.phx.gbl...
>> Hi,
>> I have 10+ SQL Servers, from my head office server i
want
>> to connect to all branch server and execute a sql
>> statement and results to be inserted into a table of
Head
>> Office Server.
>>
>> Regards
>
>.
>

Fetch data from Multiple SQL Servers

Hi,
I have 10+ SQL Servers, from my head office server i want
to connect to all branch server and execute a sql
statement and results to be inserted into a table of Head
Office Server.
RegardsYou can use a linked server... ( See linked Servers in Books on line.) Set
up a linked server then use the 4 part name
insert into mytable select * from server1.pubs.dbo.titles
etc
Or you could use replication to move the data on a regular basis...
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
"Asmath" <anonymous@.discussions.microsoft.com> wrote in message
news:1071d01c3f3c4$e43bcdd0$a001280a@.phx
.gbl...
> Hi,
> I have 10+ SQL Servers, from my head office server i want
> to connect to all branch server and execute a sql
> statement and results to be inserted into a table of Head
> Office Server.
>
> Regards|||Hi,
In my head office server, i found the list of all branch
server in Security->Remote Servers.
I found no entries in Security->LinkedServers, when i try
to add linked server, error appears already exists?
And how to use authentication process?

>--Original Message--
>You can use a linked server... ( See linked Servers in
Books on line.) Set
>up a linked server then use the 4 part name
>insert into mytable select * from server1.pubs.dbo.titles
>etc
>Or you could use replication to move the data on a
regular basis...
>--
>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
>
>"Asmath" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1071d01c3f3c4$e43bcdd0$a001280a@.phx
.gbl...
want
Head
>
>.
>

Feature requests

May be it's too late, but I think this requests could be scheduled at least
for a SP1 if it's not possible for the RTM.

1) Execute Task without debugger: it would be very nice to be able to execute
a single task without going in debugging mode. Just as you would ask "Start
Without Debugging CTRL+F5" but for a single task

2) Customize default properties for task and component: when you drag a task
on the package you get a default value for the properties that you could want
to change; often I need to change the same property in the same way each time
(for example I'd like to set the Batch Size for a OLE DB destination to 1000
instead than 0)

3) If you open a package and connections to data source are not available,
propose to "work offline" at the first failed connection.

IMHO, these features would be very important for developer productivity.

Marco Russo
http://sqljunkies.com/weblog/sqlbi

Hi Marco,

All great suggestions. Can you open them in BetaPlace? Unfortunately they're too late for SQL Server 2005 but we'd love to revisit them for the future.

For #2, Copy/Paste might be a short term solution.

regards,
ash|||I cannot find the thread where someone from Microsoft solicited suggestions for changes; I thought it was in a thread by Jamie Thompson, but somehow I am now overlooking it (or misremembering).
In any case, in the hopes that someone relevant sees this, I have three more.
* In any editor for any component, have a visible indicator on all properties which are supplanted at run-time by expressions. For example, have the values in red. This is to indicate that what you are seeing is not what will be used.
* Mark all the boxes which have event handlers attached. As above, this is to inform the human that there is more here than is apparent, and that the human should go track down the "more" (in this case, event handlers), to really find out what is happening.
* Have a list, or tree view, of all the event handlers. I've not figured out anyway to find, say 20 event handlers scattered across 500 boxes in many packages, except by the slowly going through and double-clicking on every box looking for event handlers. This seems to me a terrible way to find event handlers; I don't know if I'm overlooking something obvious (I hope), but in case not, and perhaps in any case?, this request for enhancement.

(I cannot log in to betaplace; I spent some time trying to do so, and waving my mouse around clicking on invisible buttons, and I never got past a page saying that my account would be activated someday, I think, and I cannot even remember the sequence of steps to get there again now.)|||Great ideas Perry, I second all of them. The one about indicating in the control-flow which tasks/containers have eventhandlers on them is inspired.

Your idea about a visual representation of which properties have expressions on them has already been raised. Hopefully we'll see it in the next version!

-Jamie|||Yes, I third them! In addition, it would be nice to see the ability to copy/paste/modify multiple variables. Managing variables and managing parent variables in package configurations is not easily done incurrent state, especially when you are dealing with 100+ packages all sharing same/similar variables.|||How about something that shows underlying execution plan (akin to query plan) for the entire package with cost weightage?

regards,
Nitesh|||If you've been using Integration Services and have some feedback for how to make it better, we'd love to hear more.

Please add to this thread what you'd like to see added, fixed, changed, tweeked, or removed from Integration Services.

Your feedback is valuable.
We can't promise we'll be able to make it all happen, but certainly the guidance you give here will influence planning for the next version of Integration services.

Thanks,
|||The biggest pains for me so far in designing our ETL for our warehouse have been:

- Reusing data flows, I am doing a hack that lets the data flows run over a set of tables, performing work on the common columns. What would be useful is if you can define a "table set" within SSIS and then bind a data flow to the table set (where the table set is limited to the columns/types common across all tables.) I don't know if this would have to fit into the foreach stuff, or if it would be all within the data flow itself.
- Working with tables with LOTs of columns. I have a table with about 200 columns or so that I need to do a slowly changing dimension transform on. I also need to write script components that output 200 columns for inserting into the table. The script task input/output dialog makes it painful to enter the variables one by one, and the SCD wizard makes it too painful to do it by hand, so I actually went into the XML itself and changed the stuff (carefully :)) Not sure how to address this, but another major thing that's probably more of an issue to fix is that the SCD component goes insanely slow when you double click on it if you have a whole lot of columns like me. (Takes a good 3-5 minutes to come up.)
- I posted a thread earlier, but to re-iterate -- since we can't reuse data flows most of the time nor script tasks, cut and pasting should be cleaned up a bit so the formatting doesn't get completely destroyed when you paste in a huge block of data flow/control flow tasks.
- Undo! :)
- Another small feature suggestion would be a more complex lookup task that had inherently a built in behavior for when the lookup fails. I have an "Unknown" member for each dimension, and if my lookup fails for a certain member of a fact table I need to link it to the Unknown member. What this translates to are a conditional split for if the key being looked up is NULL (or 0) and then setting it to zero if it was NULL or actually doing the lookup, and then doing a union of the rows again. I realize I could just rely upon the error output of the Lookup, but that seems broken to me since "Unknown" is an expected behavior. The ideal situation is for the Lookup Task to have an optional default value to use if the lookup fails and/or if the column being looked up is NULL.|||Great suggestions!
Keep them coming!
K|||On the note of the Lookup Task, I think it's probably an extremely common use case where you have to translate a set of fact table business keys to surrogate dimension keys. (Project REAL, for example, seems to have a huge data flow to do this, and so do I.) With this in mind, it might be useful to have one single lookup task to translate all the keys (my current package has like 15 lookup tasks and a whole lot of conditionals for the aforementioned "Unknown" behavior checking.) Having one task that has a series of "table, join key, lookup value, lookup column, default value if null or not found" would consolidate my 40-50 tasks into a single one (which probably could internally do the lookups in parallel, increasing performance.)
|||

Ok here's my wish list,

1. Advanced Editor support for >1 input. (This should enable the script component with > 1 input)

2. Read only access to the whole package from componentmetadata, not just that related to the component.

3. Parallel For each loops. Performance.

4. Option on Raw file to create once per package. This allows the same raw destination to be used in a loop

5. Debug support for script component (not just the task)

6. Parallel multicast. Says it all really performance (I know the memory issue but it should be an option. Allows for the creation of a new execution tree. It would be great if the compiler (process that produces execution tree) could figure this out. This would probably need to now the distribution of data being processed.

7. Suggest Types for flat files to provide the option of reading a whole file. This is to avoid encountering bugs during run time, which is very time consuming.

8. Suggest types for flat files to all for data to be just strings, rather than convert data to proper types. This is for performance

9. IIS Log file connection both source and destination would be good. But would settle for source.

10. Multiple data readers out of package. This would enable a package to produce multiple summaries and have them consumed by a report or other application.

11. Be able to drag a connection from one component to another. Its a real pain to delete one connection to be recreate it to the other component. This looses any data viewers

That'll do for now.

|||Thanks Simon. Excellent input. Thanks!
Anyone else?
K|||

I would like to see 3 big key improvements within SSIS. I have raised this before, Kirk asked me to send him a mail, which I never got around to do it. Sorry Kirk.

1. Data Profiler. This is quite crucial when you analyse the data to determine how bad the data is etc. Yes I know, the feature is sort of there but it is not good enough. It need to be improved considerably. We should be able to put any type of files and profile it before we start the work. Saves lot of time. It should be quick and simple to do, in the meantime it should be powerful.

2. Meta Data Management Tool. This can be web based tool / something along those line, which can be given to the business users to indetify for example, how we derive Net Sales column in the fact table. From my own experience, spent hours / days explaining how we derive each column. In a huge data warehousing environment it is very time consuming. This is not fun, i rather be writing SSIS package instead .

3. Dependancy Analysis. I would like to see a tool that would do the dependancy analysis on the fly, if I specify, that I am going to drop column A, it should run some kind of routine and tells me if you drop this column from your SSIS package, it will affect this table, cube and package etc. Run the check against the metadata only, therefore it should be quick. Save lots of time and avoid mistakes happening.

These are my requests. I know they are big requests, but I think we do need them in Microsoft environment as other competitors got similar products.

What everyone else think about these features.

Thanks
Sutha

|||I've already fed alot of stuff back to Kirk offline but just for the edification of everyone else, here are some ideas:
http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1398.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/16/1419.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/09/07/2130.aspx

-Jamie|||Sometimes .dtsx files get corrupted. Don't know why...don't know how!

It would be useful to have a tool to analyse a corrupt .dtsx file to tell you exactly what's wrong with it, how to fix it, possibly even fix it for you etc.... The error messages you get when trying to load it aren't really useful.

-Jamiesql

Feature requests

May be it's too late, but I think this requests could be scheduled at least
for a SP1 if it's not possible for the RTM.

1) Execute Task without debugger: it would be very nice to be able to execute
a single task without going in debugging mode. Just as you would ask "Start
Without Debugging CTRL+F5" but for a single task

2) Customize default properties for task and component: when you drag a task
on the package you get a default value for the properties that you could want
to change; often I need to change the same property in the same way each time
(for example I'd like to set the Batch Size for a OLE DB destination to 1000
instead than 0)

3) If you open a package and connections to data source are not available,
propose to "work offline" at the first failed connection.

IMHO, these features would be very important for developer productivity.

Marco Russo
http://sqljunkies.com/weblog/sqlbi

Hi Marco,

All great suggestions. Can you open them in BetaPlace? Unfortunately they're too late for SQL Server 2005 but we'd love to revisit them for the future.

For #2, Copy/Paste might be a short term solution.

regards,
ash|||I cannot find the thread where someone from Microsoft solicited suggestions for changes; I thought it was in a thread by Jamie Thompson, but somehow I am now overlooking it (or misremembering).
In any case, in the hopes that someone relevant sees this, I have three more.
* In any editor for any component, have a visible indicator on all properties which are supplanted at run-time by expressions. For example, have the values in red. This is to indicate that what you are seeing is not what will be used.
* Mark all the boxes which have event handlers attached. As above, this is to inform the human that there is more here than is apparent, and that the human should go track down the "more" (in this case, event handlers), to really find out what is happening.
* Have a list, or tree view, of all the event handlers. I've not figured out anyway to find, say 20 event handlers scattered across 500 boxes in many packages, except by the slowly going through and double-clicking on every box looking for event handlers. This seems to me a terrible way to find event handlers; I don't know if I'm overlooking something obvious (I hope), but in case not, and perhaps in any case?, this request for enhancement.

(I cannot log in to betaplace; I spent some time trying to do so, and waving my mouse around clicking on invisible buttons, and I never got past a page saying that my account would be activated someday, I think, and I cannot even remember the sequence of steps to get there again now.)
|||Great ideas Perry, I second all of them. The one about indicating in the control-flow which tasks/containers have eventhandlers on them is inspired.

Your idea about a visual representation of which properties have expressions on them has already been raised. Hopefully we'll see it in the next version!

-Jamie|||Yes, I third them! In addition, it would be nice to see the ability to copy/paste/modify multiple variables. Managing variables and managing parent variables in package configurations is not easily done incurrent state, especially when you are dealing with 100+ packages all sharing same/similar variables.|||How about something that shows underlying execution plan (akin to query plan) for the entire package with cost weightage?

regards,
Nitesh|||If you've been using Integration Services and have some feedback for how to make it better, we'd love to hear more.

Please add to this thread what you'd like to see added, fixed, changed, tweeked, or removed from Integration Services.

Your feedback is valuable.
We can't promise we'll be able to make it all happen, but certainly the guidance you give here will influence planning for the next version of Integration services.

Thanks,
|||The biggest pains for me so far in designing our ETL for our warehouse have been:

- Reusing data flows, I am doing a hack that lets the data flows run over a set of tables, performing work on the common columns. What would be useful is if you can define a "table set" within SSIS and then bind a data flow to the table set (where the table set is limited to the columns/types common across all tables.) I don't know if this would have to fit into the foreach stuff, or if it would be all within the data flow itself.
- Working with tables with LOTs of columns. I have a table with about 200 columns or so that I need to do a slowly changing dimension transform on. I also need to write script components that output 200 columns for inserting into the table. The script task input/output dialog makes it painful to enter the variables one by one, and the SCD wizard makes it too painful to do it by hand, so I actually went into the XML itself and changed the stuff (carefully :)) Not sure how to address this, but another major thing that's probably more of an issue to fix is that the SCD component goes insanely slow when you double click on it if you have a whole lot of columns like me. (Takes a good 3-5 minutes to come up.)
- I posted a thread earlier, but to re-iterate -- since we can't reuse data flows most of the time nor script tasks, cut and pasting should be cleaned up a bit so the formatting doesn't get completely destroyed when you paste in a huge block of data flow/control flow tasks.
- Undo! :)
- Another small feature suggestion would be a more complex lookup task that had inherently a built in behavior for when the lookup fails. I have an "Unknown" member for each dimension, and if my lookup fails for a certain member of a fact table I need to link it to the Unknown member. What this translates to are a conditional split for if the key being looked up is NULL (or 0) and then setting it to zero if it was NULL or actually doing the lookup, and then doing a union of the rows again. I realize I could just rely upon the error output of the Lookup, but that seems broken to me since "Unknown" is an expected behavior. The ideal situation is for the Lookup Task to have an optional default value to use if the lookup fails and/or if the column being looked up is NULL.

|||Great suggestions!
Keep them coming!
K|||On the note of the Lookup Task, I think it's probably an extremely common use case where you have to translate a set of fact table business keys to surrogate dimension keys. (Project REAL, for example, seems to have a huge data flow to do this, and so do I.) With this in mind, it might be useful to have one single lookup task to translate all the keys (my current package has like 15 lookup tasks and a whole lot of conditionals for the aforementioned "Unknown" behavior checking.) Having one task that has a series of "table, join key, lookup value, lookup column, default value if null or not found" would consolidate my 40-50 tasks into a single one (which probably could internally do the lookups in parallel, increasing performance.)

|||

Ok here's my wish list,

1. Advanced Editor support for >1 input. (This should enable the script component with > 1 input)

2. Read only access to the whole package from componentmetadata, not just that related to the component.

3. Parallel For each loops. Performance.

4. Option on Raw file to create once per package. This allows the same raw destination to be used in a loop

5. Debug support for script component (not just the task)

6. Parallel multicast. Says it all really performance (I know the memory issue but it should be an option. Allows for the creation of a new execution tree. It would be great if the compiler (process that produces execution tree) could figure this out. This would probably need to now the distribution of data being processed.

7. Suggest Types for flat files to provide the option of reading a whole file. This is to avoid encountering bugs during run time, which is very time consuming.

8. Suggest types for flat files to all for data to be just strings, rather than convert data to proper types. This is for performance

9. IIS Log file connection both source and destination would be good. But would settle for source.

10. Multiple data readers out of package. This would enable a package to produce multiple summaries and have them consumed by a report or other application.

11. Be able to drag a connection from one component to another. Its a real pain to delete one connection to be recreate it to the other component. This looses any data viewers

That'll do for now.

|||Thanks Simon. Excellent input. Thanks!
Anyone else?
K|||

I would like to see 3 big key improvements within SSIS. I have raised this before, Kirk asked me to send him a mail, which I never got around to do it. Sorry Kirk.

1. Data Profiler. This is quite crucial when you analyse the data to determine how bad the data is etc. Yes I know, the feature is sort of there but it is not good enough. It need to be improved considerably. We should be able to put any type of files and profile it before we start the work. Saves lot of time. It should be quick and simple to do, in the meantime it should be powerful.

2. Meta Data Management Tool. This can be web based tool / something along those line, which can be given to the business users to indetify for example, how we derive Net Sales column in the fact table. From my own experience, spent hours / days explaining how we derive each column. In a huge data warehousing environment it is very time consuming. This is not fun, i rather be writing SSIS package instead .

3. Dependancy Analysis. I would like to see a tool that would do the dependancy analysis on the fly, if I specify, that I am going to drop column A, it should run some kind of routine and tells me if you drop this column from your SSIS package, it will affect this table, cube and package etc. Run the check against the metadata only, therefore it should be quick. Save lots of time and avoid mistakes happening.

These are my requests. I know they are big requests, but I think we do need them in Microsoft environment as other competitors got similar products.

What everyone else think about these features.

Thanks
Sutha

|||I've already fed alot of stuff back to Kirk offline but just for the edification of everyone else, here are some ideas:
http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1398.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/16/1419.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/09/07/2130.aspx

-Jamie|||Sometimes .dtsx files get corrupted. Don't know why...don't know how!

It would be useful to have a tool to analyse a corrupt .dtsx file to tell you exactly what's wrong with it, how to fix it, possibly even fix it for you etc.... The error messages you get when trying to load it aren't really useful.

-Jamie

Fatal error 625 - Connection broken problem (Help needed)

Hello everyone,
When I execute the following statement in the query analyzer on a msSQL2000
database...
delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
Userid=78 and DVDID=8 and onhold=0);
I get the following error...
Server: Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
Connection Broken
If I execute a simple SELECT statement, or any other statement, it runs ok
and doesn't cause an error.
Is there anything wrong with the above statement? and how can it be causing
that error message?
Thanks in advance for all help received!
Regards, RobertI'd run DBCC CHECKDB immediately.
Linchi
"ROBinBrampton" wrote:
> Hello everyone,
> When I execute the following statement in the query analyzer on a msSQL2000
> database...
> delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
> Userid=78 and DVDID=8 and onhold=0);
> I get the following error...
> Server: Msg 21, Level 20, State 1, Line 1
> Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
> Connection Broken
> If I execute a simple SELECT statement, or any other statement, it runs ok
> and doesn't cause an error.
> Is there anything wrong with the above statement? and how can it be causing
> that error message?
> Thanks in advance for all help received!
> Regards, Robert
>

Fatal error 625 - Connection broken problem (Help needed)

Hello everyone,
When I execute the following statement in the query analyzer on a msSQL2000
database...
delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
Userid=78 and DVDID=8 and onhold=0);
I get the following error...
Server: Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
Connection Broken
If I execute a simple SELECT statement, or any other statement, it runs ok
and doesn't cause an error.
Is there anything wrong with the above statement? and how can it be causing
that error message?
Thanks in advance for all help received!
Regards, Robert
I'd run DBCC CHECKDB immediately.
Linchi
"ROBinBrampton" wrote:

> Hello everyone,
> When I execute the following statement in the query analyzer on a msSQL2000
> database...
> delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
> Userid=78 and DVDID=8 and onhold=0);
> I get the following error...
> Server: Msg 21, Level 20, State 1, Line 1
> Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
> Connection Broken
> If I execute a simple SELECT statement, or any other statement, it runs ok
> and doesn't cause an error.
> Is there anything wrong with the above statement? and how can it be causing
> that error message?
> Thanks in advance for all help received!
> Regards, Robert
>

Fatal error 625 - Connection broken problem (Help needed)

Hello everyone,
When I execute the following statement in the query analyzer on a msSQL2000
database...
delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
Userid=78 and DVDID=8 and onhold=0);
I get the following error...
Server: Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
Connection Broken
If I execute a simple SELECT statement, or any other statement, it runs ok
and doesn't cause an error.
Is there anything wrong with the above statement? and how can it be causing
that error message?
Thanks in advance for all help received!
Regards, RobertI'd run DBCC CHECKDB immediately.
Linchi
"ROBinBrampton" wrote:

> Hello everyone,
> When I execute the following statement in the query analyzer on a msSQL200
0
> database...
> delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
> Userid=78 and DVDID=8 and onhold=0);
> I get the following error...
> Server: Msg 21, Level 20, State 1, Line 1
> Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
> Connection Broken
> If I execute a simple SELECT statement, or any other statement, it runs ok
> and doesn't cause an error.
> Is there anything wrong with the above statement? and how can it be causin
g
> that error message?
> Thanks in advance for all help received!
> Regards, Robert
>

Fastest way to execute aggegate functions on a table?

I've probably not given the best title to this topic, but that
reflects my relative "newbie" status.

I have a table that goes essentially

TSDATETIME
jobnumberVARCHAR
jobentryVARCHAR
...

the TS is a time stamp, and the other two fields are job number
and entries. There are (many) more fields, but this is the core of
it. The relationship is there will be several entries per job, with
one row in the table per entry (i.e several rows per job).

In constructing a web interface I want to create a list of recent
job numbers, and summarize it broadly as follows

max(ts)
jobnumber
count(jobentry)
...

I can do this by a select command as follows

select top 30 max(ts) as time, jobnumber, count(jobentry)
from Jobs
group by jobnumber
order by time desc

However I'm now finding that this is quite slow now that my teat table
has around 400,000 entries (Even though I've added indexes to most
relevant fields). In particular it's much slower than

select top 30 jobnumber
from Jobs
group by jobnumber
order by jobnumber desc

leading me to suspect that the aggregate functions are slowing this
down. I would guesstimate the difference in speed is around a factor
of 10-20.

As I type this I think I've just realised it's the

order by time desc

that is probably causing the problem (in that it will need to
calculate max(ts) for all jobnumber's in the table in order to
execute the "order by" clause).

I think I can see the solution now (amazing what typing out a long
question can do for you :-)

My question was going to be if there was any approved method/tricks
for avoiding this sort performance hit.

It seemed to me that if I could first grab a decent number of recent
records (quickly) I could then execute the aggregate functions against
that smaller set of records. I know in broad terms how any entries
there can be per job, so I could select enough to make at least 30
jobs, and then execute the real query against that. In my case there
will be probably less than 10 entries per job, so I could grab 300
records and execute against that, instead of against the whole
400,000.

That being the case is this best done

a) as a subquery, and if so, how

b) by creating a temporary intermediate table (which is
in essence a more explicit version of (a) isn't it?)

Another solution that occurred was

c) to create and maintain a summary table, with just
one record per jobnumber with a view to having it
serve this particular (common) enquiry.

For (c) to work I would probably need triggers on the Jobs table to
maintain the summary table. This would probably represent more
overhead overall, but should certainly give a better response
when the query outlined above is executed on the web site.

The response time to this query is becoming critical on the web
interface to avoid timeouts.

Any suggestions or comments are welcome. If it's RTFM, then I'd
appreciate page numbers :-)

Thanks for reading this far :-)
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/John A Fotheringham (jaf@.jafsoft.com) writes:
> I can do this by a select command as follows
> select top 30 max(ts) as time, jobnumber, count(jobentry)
> from Jobs
> group by jobnumber
> order by time desc
> However I'm now finding that this is quite slow now that my teat table
> has around 400,000 entries (Even though I've added indexes to most
> relevant fields). In particular it's much slower than

A non-clustered index on (jobumber, time) should do wonders. Replace
count(jobentry) with count(*). The difference is that count(*) counts
all rows, count(jobentry) only count rows where jobentry is non-NULL.
Assuming that jobentry is NOT NULL, count(*) should not make a difference.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote:

>John A Fotheringham (jaf@.jafsoft.com) writes:
>> I can do this by a select command as follows
>>
>> select top 30 max(ts) as time, jobnumber, count(jobentry)
>> from Jobs
>> group by jobnumber
>> order by time desc
>>
>> However I'm now finding that this is quite slow now that my teat table
>> has around 400,000 entries (Even though I've added indexes to most
>> relevant fields). In particular it's much slower than
>A non-clustered index on (jobumber, time) should do wonders.

I have separate indexes on jobnumber, ts etc. In particular I have
added an index to each column mentioned in the "logic" of the select
but probably not on all the columns that are selected as data from the
table. Would that make a difference? It doesn't seem to be as big an
impact as using the aggregate function in the "order by" clause.

Also, what is the difference between creating

index (jobnumber, time)

and creating separate indexes on jobnumber and time? I should restate
that I'm a comparative newbie here, so still getting to grips with
these sorts of performance tuning issues, so any pointers are
appreciated.

>Replace
>count(jobentry) with count(*). The difference is that count(*) counts
>all rows, count(jobentry) only count rows where jobentry is non-NULL.
>Assuming that jobentry is NOT NULL, count(*) should not make a difference.

Probably not an issue here as jobentry is never NULL (although that's
more by implementation than table design :-)

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||John A Fotheringham (jaf@.jafsoft.com) writes:
> Also, what is the difference between creating
> index (jobnumber, time)
> and creating separate indexes on jobnumber and time? I should restate
> that I'm a comparative newbie here, so still getting to grips with
> these sorts of performance tuning issues, so any pointers are
> appreciated.

Well, try this:

SELECT jobnumber FROM tbl ORDER BY jobnumber
SELECT time FROM tbl ORDER BY time

Run these queries, and the print the result. Then use the printouts to
find the MAX(time) for each job manually. (Or at least try to imagine
how you would do it!)

When you have grown tired of this, try:

SELECT jobnumber, time FROM tbl ORDER BY jobnumber, time

print this as well, and do the same manual exercise. You will find that
what previously was a very labourous and boring task is now suddenly quite
simple.

There are two things that gain with the index (jobnumber, time):
1) The index makes it easy for SQL Server to find the max time for each
job quickly.
2) The index covers the query, which means that the query can be
computed entirely from the index, no need to access the data pages.
The index is much smaller than the data pages, since there are fewer
columns in the index. And since the index is smaller, more rows fits
one page, and SQL Server has to read fewer pages. This was why I removed
jobentry. With jobentry in the query, SQL Server would have to
access the data pages, which could lead to SQL Server opting to scan
the entire table instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'm answering my own post to document the solution I adopted

>I can do this by a select command as follows
>select top 30 max(ts) as time, jobnumber, count(jobentry)
>from Jobs
>group by jobnumber
>order by time desc

I used a subqeuery as follows

select top 30 max(ts) as time, jobnumber, count(jobentry), ...
from Jobs
where jobnumber in
(select top 1000 jobnumber from Jobs
order by ts desc)
order by time desc

In essence I grab the last 1000 records from the Jobs table (which has
nearly 500,000), and then execute the aggregate query against the
results. This is much quicker than the original query, completing in
under a second compared to 20 seconds.

The only problem is that the choice of 1000 records is a little
arbitrary as it's difficult to know what number is required to get
the 30 results wanted. In my situation I know the average number of
entries per job, and so can be confident that 1000 is ample, but the
situation becomes more complex if I want to add a "where" clause to
query (e.g. to limit to jobs allocated to a particular user), as the
number of records that need to be scanned to get 30 results will then
be larger, but in this limited and key situation this solution seems
to work for me.

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/

fastest way to do this?

I'm trying to find out what's the fastest way to execute such a query.
Presumely I have 1 denormalized table (TableTest) with 2 columns A and B
A - Nvarchar(50)
B - smalltimestamp
I have 10 records in this table out of which column A has 2 distinct values
(Test1 and Test2) and B has 10 distinct values, so it might look something
like this
A B
Test1 2006-03-31 15:21:00
Test1 2006-03-23 15:21:00
Test1 2006-02-01 15:21:00
..
Test2 2006-04-01 15:21:00
Test2 2006-03-31 15:12:00
Test2 2006-01-28 13:21:00
Now I want to execute a query on this table to return the latest occurrence
of A and B, so the results should look like this
A B
Test1 2006-03-31 15:21:00
Test2 2006-04-01 15:21:00
What is the fastest way to do this? I've tried a couple of methods but
they're all slow.I'd say that the construct which is designed for this type of operation has
the best chance for best
performance:
SELECT A, MAX(B)
FROM tblname
GROUP BY A
And then you make sure you have good indexes to support that query. Perhaps
a non-clustered index on
(A,B) is the best choice here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nestor" <n3570r@.yahoo.com> wrote in message news:%23N1WqPJVGHA.4436@.TK2MSFTNGP10.phx.gbl..
.
> I'm trying to find out what's the fastest way to execute such a query.
> Presumely I have 1 denormalized table (TableTest) with 2 columns A and B
> A - Nvarchar(50)
> B - smalltimestamp
> I have 10 records in this table out of which column A has 2 distinct value
s (Test1 and Test2) and
> B has 10 distinct values, so it might look something like this
> A B
> Test1 2006-03-31 15:21:00
> Test1 2006-03-23 15:21:00
> Test1 2006-02-01 15:21:00
> ..
> Test2 2006-04-01 15:21:00
> Test2 2006-03-31 15:12:00
> Test2 2006-01-28 13:21:00
> Now I want to execute a query on this table to return the latest occurrenc
e of A and B, so the
> results should look like this
> A B
> Test1 2006-03-31 15:21:00
> Test2 2006-04-01 15:21:00
> What is the fastest way to do this? I've tried a couple of methods but the
y're all slow.
>
>

fast query execution

i have a query which is taking 15 minutes to execute.The databse is very
large with a size of 20 gb approximately.the curruspong .ldf file to the
table is around 13gb.can any one help me to make my query execution fast.max
of 2 or 3 minutesHi
Send the DML and DDL. We can't help if we don't see what is going on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dileep" <Dileep@.discussions.microsoft.com> wrote in message
news:4C57EC53-CE00-4AA0-B938-86545EFBF088@.microsoft.com...
>i have a query which is taking 15 minutes to execute.The databse is very
> large with a size of 20 gb approximately.the curruspong .ldf file to the
> table is around 13gb.can any one help me to make my query execution
> fast.max
> of 2 or 3 minutes|||Hi
this is my query
SELECT l.LemmaId, l.BaseString, l.LanguageISODesc,l.LemmaMemo,k.NNClassName
,k.Semantic,l.ProductName, l.LemmaCreationDate,l.LemmaModificationDate
from (SELECT kbs.Lemma.LemmaId, kbs.BaseString.BaseString,
kbs.Country.CountryDescription,kbs.[Language].LanguageISODesc,
kbs.Product.ProductName
kbs.Lemma.LemmaMemo,kbs.Lemma.LemmaCreationDate,kbs.Lemma.LemmaModificationDate FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language] ON kbs.Lemma.LanguageId =kbs.[Language].LanguageId INNER JOIN kbs.Product ON kbs.Lemma.ProductId =kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%')
as l, (select i.LemmaId,i.NNClassName,j.Semantic from (SELECT DISTINCT
kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType ON kbs.NNIndication.NNIndicationTypeId =kbs.NNIndicationType.NNIndicationTypeId INNER JOIN kbs.NNClass ON
kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId where LemmaId
in(SELECT kbs.Lemma.LemmaId FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId where CountryISONr
=250 and kbs.Lemma.TableTypeId in ('2' ) and kbs.Lemma.ProductId in ('3' )and
kbs.BaseString.BaseString like 'a%') and kbs.NNClass.NNMetaClassId =3 ) as
i, (SELECT DISTINCT kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName as
Semantic FROM kbs.NNIndication INNER JOIN kbs.NNIndicationType ON
kbs.NNIndication.NNIndicationTypeId = kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass ON kbs.NNIndicationType.NNClassId =kbs.NNClass.NNClassId where LemmaId in(SELECT kbs.Lemma.LemmaId FROM
kbs.Lemma INNER JOIN
kbs.BaseString ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%') and
kbs.NNClass.NNMetaClassId =4 )as j where i.LemmaId=j.LemmaId )as k
where k.LemmaId=l.LemmaId order by BaseString
thanks and regards|||Dileep wrote:
> Hi
> this is my query
>
[snip]
Hi Dileep,
For anyone that would like to analyse this query further, I have
reformatted the query (see bottom of the post). It is best to keep the
query in a human readable format. This makes debugging easier and also
performance analysis.
Please post the accompanying DDL (simplyfied CREATE TABLE statements,
all keys, constraints and indexes, and some sample data). Please also
post estimates of the number of rows in each table and the number of
rows in the resultset. Without all this information there is not much
anyone can say.
Based on the query you posted, I only have general advice:
- Make sure you index all primary and foreign keys (all join criteria)
- Add some compound indexes, check the query plan and keep the indexes
that are used (remove the other indexes that you added). For example,
try adding an index on kbs.Lemma(TableTypeId, ProductId, BaseStringId)
and on kbs.Lemma(BaseStringId, TableTypeId, ProductId) and check which
(if any) index is used for table kbs.Lemma. Another example: add indexes
on kbs.BaseString(BaseString, BaseStringId) and on
kbs.BaseString(BaseStringId, BaseString). It is best to add indexes to
all tables before determining which are used and which are useless
- Remove the DISTINCT keywords if they are not necessary
- Remove the virtual table constructs. The current main query is
something like "SELECT <columns> FROM (<subquery1>) AS l, (<subquery2>)
AS k WHERE l.<key>=k.<key>". The main query and the 2 subqueries can be
merged into one query.
HTH,
Gert-Jan
SELECT l.LemmaId
, l.BaseString
, l.LanguageISODesc
, l.LemmaMemo
, k.NNClassName
, k.Semantic
, l.ProductName
, l.LemmaCreationDate
, l.LemmaModificationDate
from (
SELECT
kbs.Lemma.LemmaId
, kbs.BaseString.BaseString
, kbs.Country.CountryDescription
, kbs.[Language].LanguageISODesc
, kbs.Product.ProductName
, kbs.Lemma.LemmaMemo
, kbs.Lemma.LemmaCreationDate
, kbs.Lemma.LemmaModificationDate
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language]
ON kbs.Lemma.LanguageId = kbs.[Language].LanguageId
INNER JOIN kbs.Product
ON kbs.Lemma.ProductId = kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
) as l
, (
select
i.LemmaId
, i.NNClassName
, j.Semantic from (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in(
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr=250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =3
) as i
, (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName as Semantic
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in (
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =4
)as j
where i.LemmaId=j.LemmaId
)as k
where k.LemmaId=l.LemmaId
order by BaseString

fast query execution

i have a query which is taking 15 minutes to execute.The databse is very
large with a size of 20 gb approximately.the curruspong .ldf file to the
table is around 13gb.can any one help me to make my query execution fast.max
of 2 or 3 minutes
Hi
Send the DML and DDL. We can't help if we don't see what is going on.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dileep" <Dileep@.discussions.microsoft.com> wrote in message
news:4C57EC53-CE00-4AA0-B938-86545EFBF088@.microsoft.com...
>i have a query which is taking 15 minutes to execute.The databse is very
> large with a size of 20 gb approximately.the curruspong .ldf file to the
> table is around 13gb.can any one help me to make my query execution
> fast.max
> of 2 or 3 minutes
|||Hi
this is my query
SELECT l.LemmaId, l.BaseString, l.LanguageISODesc,l.LemmaMemo,k.NNClassName
,k.Semantic,l.ProductName, l.LemmaCreationDate,l.LemmaModificationDate
from (SELECT kbs.Lemma.LemmaId, kbs.BaseString.BaseString,
kbs.Country.CountryDescription,kbs.[Language].LanguageISODesc,
kbs.Product.ProductName,
kbs.Lemma.LemmaMemo,kbs.Lemma.LemmaCreationDate,kb s.Lemma.LemmaModificationDate FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language] ON kbs.Lemma.LanguageId =
kbs.[Language].LanguageId INNER JOIN kbs.Product ON kbs.Lemma.ProductId =
kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%')
as l, (select i.LemmaId,i.NNClassName,j.Semantic from (SELECT DISTINCT
kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId INNER JOIN kbs.NNClass ON
kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId where LemmaId
in(SELECT kbs.Lemma.LemmaId FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId where CountryISONr
=250 and kbs.Lemma.TableTypeId in ('2' ) and kbs.Lemma.ProductId in ('3' )and
kbs.BaseString.BaseString like 'a%') and kbs.NNClass.NNMetaClassId =3 ) as
i, (SELECT DISTINCT kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName as
Semantic FROM kbs.NNIndication INNER JOIN kbs.NNIndicationType ON
kbs.NNIndication.NNIndicationTypeId = kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass ON kbs.NNIndicationType.NNClassId =
kbs.NNClass.NNClassId where LemmaId in(SELECT kbs.Lemma.LemmaId FROM
kbs.Lemma INNER JOIN
kbs.BaseString ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%') and
kbs.NNClass.NNMetaClassId =4 )as j where i.LemmaId=j.LemmaId )as k
where k.LemmaId=l.LemmaId order by BaseString
thanks and regards
|||Dileep wrote:
> Hi
> this is my query
>
[snip]
Hi Dileep,
For anyone that would like to analyse this query further, I have
reformatted the query (see bottom of the post). It is best to keep the
query in a human readable format. This makes debugging easier and also
performance analysis.
Please post the accompanying DDL (simplyfied CREATE TABLE statements,
all keys, constraints and indexes, and some sample data). Please also
post estimates of the number of rows in each table and the number of
rows in the resultset. Without all this information there is not much
anyone can say.
Based on the query you posted, I only have general advice:
- Make sure you index all primary and foreign keys (all join criteria)
- Add some compound indexes, check the query plan and keep the indexes
that are used (remove the other indexes that you added). For example,
try adding an index on kbs.Lemma(TableTypeId, ProductId, BaseStringId)
and on kbs.Lemma(BaseStringId, TableTypeId, ProductId) and check which
(if any) index is used for table kbs.Lemma. Another example: add indexes
on kbs.BaseString(BaseString, BaseStringId) and on
kbs.BaseString(BaseStringId, BaseString). It is best to add indexes to
all tables before determining which are used and which are useless
- Remove the DISTINCT keywords if they are not necessary
- Remove the virtual table constructs. The current main query is
something like "SELECT <columns> FROM (<subquery1>) AS l, (<subquery2>)
AS k WHERE l.<key>=k.<key>". The main query and the 2 subqueries can be
merged into one query.
HTH,
Gert-Jan
SELECT l.LemmaId
, l.BaseString
, l.LanguageISODesc
, l.LemmaMemo
, k.NNClassName
, k.Semantic
, l.ProductName
, l.LemmaCreationDate
, l.LemmaModificationDate
from (
SELECT
kbs.Lemma.LemmaId
, kbs.BaseString.BaseString
, kbs.Country.CountryDescription
, kbs.[Language].LanguageISODesc
, kbs.Product.ProductName
, kbs.Lemma.LemmaMemo
, kbs.Lemma.LemmaCreationDate
, kbs.Lemma.LemmaModificationDate
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language]
ON kbs.Lemma.LanguageId = kbs.[Language].LanguageId
INNER JOIN kbs.Product
ON kbs.Lemma.ProductId = kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
) as l
, (
select
i.LemmaId
, i.NNClassName
, j.Semantic from (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in(
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr=250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =3
) as i
, (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName as Semantic
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in (
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =4
)as j
where i.LemmaId=j.LemmaId
)as k
where k.LemmaId=l.LemmaId
order by BaseString

fast query execution

i have a query which is taking 15 minutes to execute.The databse is very
large with a size of 20 gb approximately.the curruspong .ldf file to the
table is around 13gb.can any one help me to make my query execution fast.max
of 2 or 3 minutesHi
Send the DML and DDL. We can't help if we don't see what is going on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dileep" <Dileep@.discussions.microsoft.com> wrote in message
news:4C57EC53-CE00-4AA0-B938-86545EFBF088@.microsoft.com...
>i have a query which is taking 15 minutes to execute.The databse is very
> large with a size of 20 gb approximately.the curruspong .ldf file to the
> table is around 13gb.can any one help me to make my query execution
> fast.max
> of 2 or 3 minutes|||Hi
this is my query
SELECT l.LemmaId, l.BaseString, l.LanguageISODesc,l.LemmaMemo,k.NNClassName
,k.Semantic,l.ProductName, l.LemmaCreationDate,l.LemmaModificationDate
from (SELECT kbs.Lemma.LemmaId, kbs.BaseString.BaseString,
kbs.Country.CountryDescription,kbs.[Language].LanguageISODesc,
kbs.Product.ProductName,
kbs.Lemma.LemmaMemo,kbs.Lemma.LemmaCreationDate,kbs.Lemma.LemmaModificationD
ate FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language] ON kbs.Lemma.LanguageId =
kbs.[Language].LanguageId INNER JOIN kbs.Product ON kbs.Lemma.ProductId
=
kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%')
as l, (select i.LemmaId,i.NNClassName,j.Semantic from (SELECT DISTINCT
kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId INNER JOIN kbs.NNClass ON
kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId where LemmaId
in(SELECT kbs.Lemma.LemmaId FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId where CountryISONr
=250 and kbs.Lemma.TableTypeId in ('2' ) and kbs.Lemma.ProductId in ('3' )an
d
kbs.BaseString.BaseString like 'a%') and kbs.NNClass.NNMetaClassId =3 ) as
i, (SELECT DISTINCT kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName as
Semantic FROM kbs.NNIndication INNER JOIN kbs.NNIndicationType ON
kbs.NNIndication.NNIndicationTypeId = kbs.NNIndicationType.NNIndicationTypeI
d
INNER JOIN kbs.NNClass ON kbs.NNIndicationType.NNClassId =
kbs.NNClass.NNClassId where LemmaId in(SELECT kbs.Lemma.LemmaId FROM
kbs.Lemma INNER JOIN
kbs.BaseString ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%') and
kbs.NNClass.NNMetaClassId =4 )as j where i.LemmaId=j.LemmaId )as k
where k.LemmaId=l.LemmaId order by BaseString
thanks and regards|||Dileep wrote:
> Hi
> this is my query
>
[snip]
Hi Dileep,
For anyone that would like to analyse this query further, I have
reformatted the query (see bottom of the post). It is best to keep the
query in a human readable format. This makes debugging easier and also
performance analysis.
Please post the accompanying DDL (simplyfied CREATE TABLE statements,
all keys, constraints and indexes, and some sample data). Please also
post estimates of the number of rows in each table and the number of
rows in the resultset. Without all this information there is not much
anyone can say.
Based on the query you posted, I only have general advice:
- Make sure you index all primary and foreign keys (all join criteria)
- Add some compound indexes, check the query plan and keep the indexes
that are used (remove the other indexes that you added). For example,
try adding an index on kbs.Lemma(TableTypeId, ProductId, BaseStringId)
and on kbs.Lemma(BaseStringId, TableTypeId, ProductId) and check which
(if any) index is used for table kbs.Lemma. Another example: add indexes
on kbs.BaseString(BaseString, BaseStringId) and on
kbs.BaseString(BaseStringId, BaseString). It is best to add indexes to
all tables before determining which are used and which are useless
- Remove the DISTINCT keywords if they are not necessary
- Remove the virtual table constructs. The current main query is
something like "SELECT <columns> FROM (<subquery1> ) AS l, (<subquery2> )
AS k WHERE l.<key>=k.<key>". The main query and the 2 subqueries can be
merged into one query.
HTH,
Gert-Jan
SELECT l.LemmaId
, l.BaseString
, l.LanguageISODesc
, l.LemmaMemo
, k.NNClassName
, k.Semantic
, l.ProductName
, l.LemmaCreationDate
, l.LemmaModificationDate
from (
SELECT
kbs.Lemma.LemmaId
, kbs.BaseString.BaseString
, kbs.Country.CountryDescription
, kbs.[Language].LanguageISODesc
, kbs.Product.ProductName
, kbs.Lemma.LemmaMemo
, kbs.Lemma.LemmaCreationDate
, kbs.Lemma.LemmaModificationDate
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language]
ON kbs.Lemma.LanguageId = kbs.[Language].LanguageId
INNER JOIN kbs.Product
ON kbs.Lemma.ProductId = kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
) as l
, (
select
i.LemmaId
, i.NNClassName
, j.Semantic from (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in(
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr=250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =3
) as i
, (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName as Semantic
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =
kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in (
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =4
)as j
where i.LemmaId=j.LemmaId
)as k
where k.LemmaId=l.LemmaId
order by BaseString

Failure Parsing The Config File

I am trying to execute the rsactivate file and I am recieving the following
error:
"Failure parsing the config file. Invalid URI: The hostname could not be
parsed."
What could be causing this and what is the solution? Thank you in advance.Check through the various config files under the Reporting Services
installation folder. What is in there for the machine name of the reporting
server in the various places?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Help Me Rhonda" <HelpMeRhonda@.discussions.microsoft.com> wrote in message
news:934397D1-52A9-4144-AE7E-997DB44259C1@.microsoft.com...
>I am trying to execute the rsactivate file and I am recieving the following
> error:
> "Failure parsing the config file. Invalid URI: The hostname could not be
> parsed."
> What could be causing this and what is the solution? Thank you in advance.
>|||Jeff,
I reviewed my config files and the RSWebApplication.config and
RSReportServer.config files all refer to the correct computer name.
Do you have any other suggestions?
Thank you.
"Jeff A. Stucker" wrote:
> Check through the various config files under the Reporting Services
> installation folder. What is in there for the machine name of the reporting
> server in the various places?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Help Me Rhonda" <HelpMeRhonda@.discussions.microsoft.com> wrote in message
> news:934397D1-52A9-4144-AE7E-997DB44259C1@.microsoft.com...
> >I am trying to execute the rsactivate file and I am recieving the following
> > error:
> >
> > "Failure parsing the config file. Invalid URI: The hostname could not be
> > parsed."
> >
> > What could be causing this and what is the solution? Thank you in advance.
> >
>
>