optimizing load performance using partitioned tables in 2005

Hi,
Iam curious to know if i can increase my load performance using
partitioned tables. If i create a 4-way partitioned table, can i load
directly into a specific partition, so effectively having 4 parallel loads
into the 4 partitions. (Something i can do in Sybase).
If not what can i do to maximize my load on a partitioned table?
Vivek
This is one option, but does your data align itself with these partitions?
Supposed you partition on last name, are your last names going to be evenly
distributed? Last names are a pretty good choice as the distribution is
somewhat even, however a choice like date is probably not so good if your
data is ordered already.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vivek" <Vivek@.discussions.microsoft.com> wrote in message
news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
> Hi,
> Iam curious to know if i can increase my load performance using
> partitioned tables. If i create a 4-way partitioned table, can i load
> directly into a specific partition, so effectively having 4 parallel loads
> into the 4 partitions. (Something i can do in Sybase).
> If not what can i do to maximize my load on a partitioned table?
> Vivek
|||Yes my data does align itself with these partitions. So what i want to know
is how do I load data into a specific partition? (using say BCP or Bulk
Insert) What is the syntax?
"Hilary Cotter" wrote:

> This is one option, but does your data align itself with these partitions?
> Supposed you partition on last name, are your last names going to be evenly
> distributed? Last names are a pretty good choice as the distribution is
> somewhat even, however a choice like date is probably not so good if your
> data is ordered already.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Vivek" <Vivek@.discussions.microsoft.com> wrote in message
> news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
>
>

optimizing load performance using partitioned tables in 2005

Hi,
Iam curious to know if i can increase my load performance using
partitioned tables. If i create a 4-way partitioned table, can i load
directly into a specific partition, so effectively having 4 parallel loads
into the 4 partitions. (Something i can do in Sybase).
If not what can i do to maximize my load on a partitioned table?
VivekThis is one option, but does your data align itself with these partitions?
Supposed you partition on last name, are your last names going to be evenly
distributed? Last names are a pretty good choice as the distribution is
somewhat even, however a choice like date is probably not so good if your
data is ordered already.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vivek" <Vivek@.discussions.microsoft.com> wrote in message
news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
> Hi,
> Iam curious to know if i can increase my load performance using
> partitioned tables. If i create a 4-way partitioned table, can i load
> directly into a specific partition, so effectively having 4 parallel loads
> into the 4 partitions. (Something i can do in Sybase).
> If not what can i do to maximize my load on a partitioned table?
> Vivek|||Yes my data does align itself with these partitions. So what i want to know
is how do I load data into a specific partition? (using say BCP or Bulk
Insert) What is the syntax?
"Hilary Cotter" wrote:

> This is one option, but does your data align itself with these partitions?
> Supposed you partition on last name, are your last names going to be evenl
y
> distributed? Last names are a pretty good choice as the distribution is
> somewhat even, however a choice like date is probably not so good if your
> data is ordered already.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Vivek" <Vivek@.discussions.microsoft.com> wrote in message
> news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
>
>

optimizing load performance using partitioned tables in 2005

Hi,
Iam curious to know if i can increase my load performance using
partitioned tables. If i create a 4-way partitioned table, can i load
directly into a specific partition, so effectively having 4 parallel loads
into the 4 partitions. (Something i can do in Sybase).
If not what can i do to maximize my load on a partitioned table?
VivekThis is one option, but does your data align itself with these partitions?
Supposed you partition on last name, are your last names going to be evenly
distributed? Last names are a pretty good choice as the distribution is
somewhat even, however a choice like date is probably not so good if your
data is ordered already.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vivek" <Vivek@.discussions.microsoft.com> wrote in message
news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
> Hi,
> Iam curious to know if i can increase my load performance using
> partitioned tables. If i create a 4-way partitioned table, can i load
> directly into a specific partition, so effectively having 4 parallel loads
> into the 4 partitions. (Something i can do in Sybase).
> If not what can i do to maximize my load on a partitioned table?
> Vivek|||Yes my data does align itself with these partitions. So what i want to know
is how do I load data into a specific partition? (using say BCP or Bulk
Insert) What is the syntax?
"Hilary Cotter" wrote:
> This is one option, but does your data align itself with these partitions?
> Supposed you partition on last name, are your last names going to be evenly
> distributed? Last names are a pretty good choice as the distribution is
> somewhat even, however a choice like date is probably not so good if your
> data is ordered already.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Vivek" <Vivek@.discussions.microsoft.com> wrote in message
> news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
> >
> > Hi,
> >
> > Iam curious to know if i can increase my load performance using
> > partitioned tables. If i create a 4-way partitioned table, can i load
> > directly into a specific partition, so effectively having 4 parallel loads
> > into the 4 partitions. (Something i can do in Sybase).
> > If not what can i do to maximize my load on a partitioned table?
> >
> > Vivek
>
>

Optimizing Job fails on tables with computed fields

Hi,
we have some tables in SQL Server 2000 with computed fields. When we try to
run an optimization job, it return the error
"Rebuilding indexes for table 'FI_Period'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'"
After removing the field the job runs without errors. I cannot see any link
between a computed field and the Quoted Identifier setting.
Any input is appreciated.
TIA, Norbert Meiss
See MSKB 301292 <http://support.microsoft.com/kb/q301292/>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:436B7DE5-21CC-413B-A0D6-854FD3721894@.microsoft.com...
> Hi,
> we have some tables in SQL Server 2000 with computed fields. When we try
> to
> run an optimization job, it return the error
> "Rebuilding indexes for table 'FI_Period'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
> have
> incorrect settings: 'QUOTED_IDENTIFIER'"
> After removing the field the job runs without errors. I cannot see any
> link
> between a computed field and the Quoted Identifier setting.
> Any input is appreciated.
> TIA, Norbert Meiss

Optimizing Job fails on tables with computed fields

Hi,
we have some tables in SQL Server 2000 with computed fields. When we try to
run an optimization job, it return the error
"Rebuilding indexes for table 'FI_Period'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'"
After removing the field the job runs without errors. I cannot see any link
between a computed field and the Quoted Identifier setting.
Any input is appreciated.
TIA, Norbert MeissSee MSKB 301292 <http://support.microsoft.com/kb/q301292/>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:436B7DE5-21CC-413B-A0D6-854FD3721894@.microsoft.com...
> Hi,
> we have some tables in SQL Server 2000 with computed fields. When we try
> to
> run an optimization job, it return the error
> "Rebuilding indexes for table 'FI_Period'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
> have
> incorrect settings: 'QUOTED_IDENTIFIER'"
> After removing the field the job runs without errors. I cannot see any
> link
> between a computed field and the Quoted Identifier setting.
> Any input is appreciated.
> TIA, Norbert Meiss

Optimizing Job fails on tables with computed fields

Hi,
we have some tables in SQL Server 2000 with computed fields. When we try to
run an optimization job, it return the error
"Rebuilding indexes for table 'FI_Period'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options
have
incorrect settings: 'QUOTED_IDENTIFIER'"
After removing the field the job runs without errors. I cannot see any link
between a computed field and the Quoted Identifier setting.
Any input is appreciated.
TIA, Norbert MeissSee MSKB 301292 <http://support.microsoft.com/kb/q301292/>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:436B7DE5-21CC-413B-A0D6-854FD3721894@.microsoft.com...
> Hi,
> we have some tables in SQL Server 2000 with computed fields. When we try
> to
> run an optimization job, it return the error
> "Rebuilding indexes for table 'FI_Period'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC
> SQL
> Server Driver][SQL Server]DBCC failed because the following SET option
s
> have
> incorrect settings: 'QUOTED_IDENTIFIER'"
> After removing the field the job runs without errors. I cannot see any
> link
> between a computed field and the Quoted Identifier setting.
> Any input is appreciated.
> TIA, Norbert Meiss

Optimizing a JOIN

I have two tables.

One has approx 90,000 rows with a field .. let's call in BigInt (and it
is defined as a bigint data type).

I have a reference table, with approx 10,000,000 rows. In this
reference table, I have starting_bigint and ending_bigint fields. I
want to pull out all of the reference data from the reference table for
all 90,000 rows in the transaction table where the BigInt from the
transaction table is between the starting_bigint and ending_bigint in
the reference table.

I have the join working now, but it is not as optimized as I would
like. It appears no matter what I do, the query does a full table scan
on the 10,000,000 rows in the reference table.

Sample code

SELECT ref.*, tran.bigint
from transactiontable tran
INNER JOIN referencetable ref on tran.bigint between
ref.starting_bigint and ending_bigint

Yes, all 3 of the fields are indexed. I even have a composite index on
the reference table with the starting_bigint and ending_bigint fields
selected as the composite.

Any help would be appreciated.

Robert H. Kershberg
IT Director
Tax Credit Company
RKershberg@.taxcc.com or RKershberg@.pobox.com or RKershberg@.gmail.comIf the starting and ending bigints ranges are not overlapping, then I
would classify this as the "zipcode problem".

If this is the case, you could try the following approach:

SELECT R1.*,T1.bigint
FROM transactiontable T1
CROSS JOIN referencetable R1
WHERE R1.starting_bigint = (
SELECT MAX(starting_bigint)
FROM referencetable R2
WHERE R2.starting_bigint <= T1.bigint
)
AND R1.ending_bigint >= T1.bigint

Hope this helps,
Gert-Jan

"rkershberg@.gmail.com" wrote:
> I have two tables.
> One has approx 90,000 rows with a field .. let's call in BigInt (and it
> is defined as a bigint data type).
> I have a reference table, with approx 10,000,000 rows. In this
> reference table, I have starting_bigint and ending_bigint fields. I
> want to pull out all of the reference data from the reference table for
> all 90,000 rows in the transaction table where the BigInt from the
> transaction table is between the starting_bigint and ending_bigint in
> the reference table.
> I have the join working now, but it is not as optimized as I would
> like. It appears no matter what I do, the query does a full table scan
> on the 10,000,000 rows in the reference table.
> Sample code
> SELECT ref.*, tran.bigint
> from transactiontable tran
> INNER JOIN referencetable ref on tran.bigint between
> ref.starting_bigint and ending_bigint
> Yes, all 3 of the fields are indexed. I even have a composite index on
> the reference table with the starting_bigint and ending_bigint fields
> selected as the composite.
> Any help would be appreciated.
> Robert H. Kershberg
> IT Director
> Tax Credit Company
> RKershberg@.taxcc.com or RKershberg@.pobox.com or RKershberg@.gmail.com|||try skipping the "between." something like
SELECT ref.*, tran.bigint
from transactiontable tran
INNER JOIN referencetable ref
on tran.bigint >= ref.starting_bigint
and tran.bigint <=ref.ending_bigint

try creating a composite index on ref, containing starting_bigint plus
ending_bigint
if that doesn't work, try zapping your existing indexes on ref.

thinking outside the box, try creating staging tables, or ghost tables.
The ghost tables are for a selected period taht you are currently
working on. You copy pieces of your trans table off to a temp table,
and do your lookups against it. you eat the one copy, you eat the
creation of the indexon the temp table, but you get the benefits of not
having to do complex searches against a 100,000,000 row table. this is
RARELY needed, but I've done it on rare occasion.|||Thank you for your time. I had tried a cross join, but not quite the
same way. It'll be interesting to see if there is a significant
difference. I appreciate your intelligent input.

Rob|||Thanks again .. this logic improved performance 3 to 4 fold (I had
already cut a 2 min 30 sec query to 15 seconds .. your logic
implemented on top of mine cut that down to 3 seconds .. this on 88,000
row transacation table running against a 9.6 million row reference
table .. not zip codes, but the right idea <g>).

Thank YOU very much!!!

Rob|||3 seconds is a long time.

how do we make it faster??

i am not a huge fan of clustered indexes. However for a reference
table where you will ALWAYS be looking up data utilizing a specific
column, and rarely inserting into the middle, I'd sure consider it.

For grins, try creating a clustered, composite index on starting_bigint
+ ending_bigint.

For grins after that, consider a clustered index on trans.bigint.
warning: clustered indexes on trans tables are usually not a great idea
IMO, unless the clustered index is based on a timestamp or an identity
column.

-doug

Optimized way of Importing more than 300000 lines of xml data into SQL

Hi,

We have a 120 MB huge relational xml file, that we parse it through xsd file and import it into SQL Server relational tables using .net Dataset class. It takes around 2 hours to validate and import this huge file into sql server.

When the import is happening most of the Server CPU and memory resources are also more than 50%.

We would like to increase the performance of this import and we are ready to redesign the application if we find a better solution.

Any ideas/suggestions sincerely appreciated.

javed.

Hi Javed,

You can use XML Bulk Load component to import your xml data directly into SQL Server.

Refer Books on line SQL Server 2005 :

SQL Server 2005 Books online ->

SQL Server Programming Reference ->

SQL XML 4.0 Programming ->

Performing Bulk Load of XML Data (SQLXML 4.0)

Refer the following links to know more about XmlBulkLoad component:

Performing Bulk Load of XML Data (SQLXML 4.0)

Using SQL Server's XML Support > XML Bulk Load

How to import XML into SQL Server with the XML Bulk Load component

Thanks

Naras.

Optimize Temp tables

Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
With that much code running just to generate a report, you might consider
having a background process (or a data warehouse) pre-aggregate the data.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||In addition to Aaron's response , create a temp tables at the beginning of
the stored procedure (reduce recompile).
Also, it may help to improve performance if you add NC/CI indexes depending
on your requrements.
Look at an execution plan of the stored procedure to ensure that optimizer
is available to use indexes.
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegr oups.com...
> Hi All,
> My application makes heavy use of temp tables because we have a few
> thousands of stored proc running every day to renerate reports.
> What are the positive and negative impact to add clustered index on the
> temp table?
> what is the negative impact if I use derived table instead temp table?
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
> Thanks so much,
> Silaphet,
>
|||Another possibility would be to consider an INDEXED VIEW.
Typically, you want to keep your OLTP and DSS/OLAP operations and data
seperated. It might be wise to reconsider the cohosting of this data in the
same database and reconsider creating something new elsewhere that better
fits your architecture.
Sincerely,
Anthony Thomas

"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegr oups.com...
Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,

Optimize Temp tables

Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
With that much code running just to generate a report, you might consider
having a background process (or a data warehouse) pre-aggregate the data.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||In addition to Aaron's response , create a temp tables at the beginning of
the stored procedure (reduce recompile).
Also, it may help to improve performance if you add NC/CI indexes depending
on your requrements.
Look at an execution plan of the stored procedure to ensure that optimizer
is available to use indexes.
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
> Hi All,
> My application makes heavy use of temp tables because we have a few
> thousands of stored proc running every day to renerate reports.
> What are the positive and negative impact to add clustered index on the
> temp table?
> what is the negative impact if I use derived table instead temp table?
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
> Thanks so much,
> Silaphet,
>|||Another possibility would be to consider an INDEXED VIEW.
Typically, you want to keep your OLTP and DSS/OLAP operations and data
seperated. It might be wise to reconsider the cohosting of this data in the
same database and reconsider creating something new elsewhere that better
fits your architecture.
Sincerely,
Anthony Thomas
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,

Optimize Temp tables

Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
With that much code running just to generate a report, you might consider
having a background process (or a data warehouse) pre-aggregate the data.
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||In addition to Aaron's response , create a temp tables at the beginning of
the stored procedure (reduce recompile).
Also, it may help to improve performance if you add NC/CI indexes depending
on your requrements.
Look at an execution plan of the stored procedure to ensure that optimizer
is available to use indexes.
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
> Hi All,
> My application makes heavy use of temp tables because we have a few
> thousands of stored proc running every day to renerate reports.
> What are the positive and negative impact to add clustered index on the
> temp table?
> what is the negative impact if I use derived table instead temp table?
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
> Thanks so much,
> Silaphet,
>|||Another possibility would be to consider an INDEXED VIEW.
Typically, you want to keep your OLTP and DSS/OLAP operations and data
seperated. It might be wise to reconsider the cohosting of this data in the
same database and reconsider creating something new elsewhere that better
fits your architecture.
Sincerely,
Anthony Thomas
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,

optimize nologging

Hi,
We have a reporting database with simple recovery model.
To improve performance we have to use SELECT..INTO clause
and create all tables...but problem now is that each
table is populating from 3-4 different result set...so if
we use SELECT ..INTO for first load(we can't use UNION in
SELECT..INTO CLAUSE) then for next 3-4 loads we have to
use INSERT INTO SELECT clause that will do lot of logging.
What are the possible options that we can use in this
scenario?
For temporary solution we are thinking of using SELECT
INTO and create 4 temp tables then bcp out the data and
then use BULK INSERT into origional table --what can be
possible flaws in this scenario?
Thanks
--HarvinderYou can use a derived table in the select statement of the select into, for
example:
SELECT column_1, column_2 INTO new_table
FROM
(SELECT column_1, column_2 FROM table_1
UNION ALL
SELECT column_1, column_2 FROM table_2) AS old_table
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"harvinder" <hs@.metratech.com> wrote in message
news:072401c3787a$cf8048a0$a001280a@.phx.gbl...
> Hi,
> We have a reporting database with simple recovery model.
> To improve performance we have to use SELECT..INTO clause
> and create all tables...but problem now is that each
> table is populating from 3-4 different result set...so if
> we use SELECT ..INTO for first load(we can't use UNION in
> SELECT..INTO CLAUSE) then for next 3-4 loads we have to
> use INSERT INTO SELECT clause that will do lot of logging.
> What are the possible options that we can use in this
> scenario?
> For temporary solution we are thinking of using SELECT
> INTO and create 4 temp tables then bcp out the data and
> then use BULK INSERT into origional table --what can be
> possible flaws in this scenario?
> Thanks
> --Harvinder
>

optimize insert to access DB

Hi,

I'm using access tables to store my data. to retrive data I use jet odbc engine in c++.

I want to insert a large amuont of records (about 20 mega records) to my database in the fasts way i can. if i use sql syntax (insert into table ()....) it's takes for ages (about 500 records per second).

if i'm writing a csv file and then use import (via access) it's much faster but here I have two problems

1.I dont know how to use the access import tool from c++.

2.I dont think I can distributie the access import tool with my product.

so my questions are :

1. Does any know any tool that insert records in an optimize way?

2. How can I use the access import tool in c++?

3. Can i use the jet engine to import csv files?

thank ishay

This forum is dedicated to SSIS. You may get better luck asking this question in the appropriate forum.

You could use SSIS to do this, and it should bulk insert into Access using the OleDB Destination Component. You won't be able to distribute it to your users though.

Could you mark this thread as answered if you have no further questions so we can close it out? Thanks!

Optimize for Many Tables and Temp files

We are using the Import/Export wizard to create some simple packages to transfer tables. When doing a lrge number of tables, the 'Optimize for many tables' option is automatically selected (as noted in BOL). What we've found is that the package creates a bunch of temp files in the creator's Documents and Settings....Temp folder. Needless to say this package cannot be re-run later, nor scheduled, since the path referenced doesn't necessarily exist on the server. Is there a way to specify where these files should be created so thatthe package is re-usable and still be optimized?

Steve

Yes, we already had people complaining about this.

There are a few options to workaround this, though:

1. Move all those files to the safe location and update the package accordingly.

2. If your number of tables is not too big (less than 100 should work on an average machine), uncheck the "Optimize for many tables" option and see if it runs for you. If it does not run, create a few sequential data flows and split the simple flows that the wizard had placed in the single Data Flow task into several of them. You can use the Copy/Paste for that.

HTH.

Optimizations on System Tables

Hi

I have an issue where my system tables are heavily fragmented. Currently our system creates between 200 and 400 new logins daily (its the method the application handles it security). However we have had execution times on this of between 13ms and 1900ms, very inconsistent.

I have seen in a previous thread http://dbforums.com/arch/70/2003/11/965415
that its Kalen Delaney's opinion that fragmentation doesn't really matter unless you have 'several dozen' pages

Here is an example of our sysxlogins table's report :

DBCC SHOWCONTIG scanning 'sysxlogins' table...
Table: 'sysxlogins' (33); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 162
- Extents Scanned.......................: 35
- Extent Switches.......................: 156
- Avg. Pages per Extent..................: 4.6
- Scan Density [Best Count:Actual Count]......: 13.38% [21:157]
- Logical Scan Fragmentation ..............: 49.38%
- Extent Scan Fragmentation ...............: 34.29%
- Avg. Bytes Free per Page................: 5694.6
- Avg. Page Density (full)................: 29.64%

As you can see, its got 162 pages, which probably counts as 'several' dozen.

I would like to know if its possible for us to defrag/reindex this table, as you can see, it has a VERY low Scan Density, and a rather high Fragmentation level.

Opinions, help, undocumented functionality all welcome
TiaanI am facing a same issue.
I have noticed that my system table are extremly fragmented. Take a look. Any suggestions? Or does it not matter that they are like that?

DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 23
- Extents Scanned.......................: 7
- Extent Switches.......................: 21
- Avg. Pages per Extent..................: 3.3
- Scan Density [Best Count:Actual Count]......: 13.64% [3:22]
- Logical Scan Fragmentation ..............: 43.48%
- Extent Scan Fragmentation ...............: 71.43%
- Avg. Bytes Free per Page................: 2744.6
- Avg. Page Density (full)................: 66.09%

DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3019.0
- Avg. Page Density (full)................: 62.70%

DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 85
- Extents Scanned.......................: 17
- Extent Switches.......................: 81
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 13.41% [11:82]
- Logical Scan Fragmentation ..............: 44.71%
- Extent Scan Fragmentation ...............: 70.59%
- Avg. Bytes Free per Page................: 3348.5
- Avg. Page Density (full)................: 58.63%

DBCC SHOWCONTIG scanning 'syscomments' table...
Table: 'syscomments' (6); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 984
- Extents Scanned.......................: 159
- Extent Switches.......................: 769
- Avg. Pages per Extent..................: 6.2
- Scan Density [Best Count:Actual Count]......: 15.97% [123:770]
- Logical Scan Fragmentation ..............: 46.24%
- Extent Scan Fragmentation ...............: 18.24%
- Avg. Bytes Free per Page................: 3261.0
- Avg. Page Density (full)................: 59.71%

DBCC SHOWCONTIG scanning 'syspermissions' table...
Table: 'syspermissions' (9); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 3
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.3
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 3771.5
- Avg. Page Density (full)................: 53.40%

DBCC SHOWCONTIG scanning 'sysdepends' table...
Table: 'sysdepends' (12); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 29
- Extents Scanned.......................: 8
- Extent Switches.......................: 26
- Avg. Pages per Extent..................: 3.6
- Scan Density [Best Count:Actual Count]......: 14.81% [4:27]
- Logical Scan Fragmentation ..............: 48.28%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3096.8
- Avg. Page Density (full)................: 61.74%

DBCC SHOWCONTIG scanning 'sysxlogins' table...
Table: 'sysxlogins' (33); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 10
- Extents Scanned.......................: 4
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 2.5
- Scan Density [Best Count:Actual Count]......: 28.57% [2:7]
- Logical Scan Fragmentation ..............: 60.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3522.2
- Avg. Page Density (full)................: 56.48%

DBCC SHOWCONTIG scanning 'sysmessages' table...
Table: 'sysmessages' (36); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 169
- Extents Scanned.......................: 23
- Extent Switches.......................: 26
- Avg. Pages per Extent..................: 7.3
- Scan Density [Best Count:Actual Count]......: 81.48% [22:27]
- Logical Scan Fragmentation ..............: 1.78%
- Extent Scan Fragmentation ...............: 13.04%
- Avg. Bytes Free per Page................: 3926.9
- Avg. Page Density (full)................: 51.48%

DBCC SHOWCONTIG scanning 'syslanguages' table...
Table: 'syslanguages' (44); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.5
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3206.0
- Avg. Page Density (full)................: 60.39%

DBCC SHOWCONTIG scanning 'syscharsets' table...
Table: 'syscharsets' (45); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 33
- Extents Scanned.......................: 6
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 5.5
- Scan Density [Best Count:Actual Count]......: 62.50% [5:8]
- Logical Scan Fragmentation ..............: 24.24%
- Extent Scan Fragmentation ...............: 16.67%
- Avg. Bytes Free per Page................: 2896.6
- Avg. Page Density (full)................: 64.21%

DBCC SHOWCONTIG scanning 'sysaltfiles' table...
Table: 'sysaltfiles' (94); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.8
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 14.29%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 4283.4
- Avg. Page Density (full)................: 47.08%

DBCC SHOWCONTIG scanning 'spt_values' table...
Table: 'spt_values' (85575343); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 4
- Extent Switches.......................: 5
- Avg. Pages per Extent..................: 1.8
- Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
- Logical Scan Fragmentation ..............: 28.57%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 2671.7
- Avg. Page Density (full)................: 66.99%|||You need not worry about fragmentation on System tables and use DBCC UPDATEUSAGE in order to correct the inconsistency.

Optimizations

I currently have a nightly process that truncates a set of tables prior to
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
--
DrewDrew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of the
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless the
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew

Optimizations

I currently have a nightly process that truncates a set of tables prior to
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
Drew
Drew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of the
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless the
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
sql

Optimizations

I currently have a nightly process that truncates a set of tables prior to
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
--
DrewDrew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of th
e
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless th
e
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail
.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew

Optimization tips for Cursor and Scope_Identity

...using SQL 2K
...This is for a data migration project which involves the splitting of a
flat file into two or more tables with relationships. The migration will
happen over time and not at one time.
...My SQL Server/DB experience up to this point has been primarily focused
on client side request, but am pretty short on optimization... so I need som
e
direction here. I can research via Google, BOL and forums, but I don't even
know where to start.
I am currently using a cursor to walk through rows in tblMigration and build
an insert statement for tblParent. I am using the cursor becuase I need to
capture the new identity value (SCOPE_IDENTITY) after each insert into
tblParent and use it to migrate the rest of the records destined for the
various child tables in the new schema.
I am moving data from one server to another and have set up a linked server.
currently a migration of 53,000 records is taking 42 min (on my dev box:
WINXP, 1GB ram, 2 GHZ).
I originally wanted to do the following, but was told this would play havoc
with replication
1. ALTER tblParent ADD old_id BIGINT
2. INSERT tblParent (field1,field2,old_id) SELECT field1, field2, id FROM
tblMigration WHERE field1='parent'
3. do remaing migration relating the tblParent.old_id to tblMIgration.id fie
ld
4. and then drop the old_id collumn from tblParent
Once again, I have not posted actual TSQL because it would be very long and
I just need the pointer in the right direction... but if that would help, le
t
me know.
thanks
KevinHavoc with replication? How?

> I originally wanted to do the following, but was told this would play
> havoc
> with replication
> 1. ALTER tblParent ADD old_id BIGINT
> 2. INSERT tblParent (field1,field2,old_id) SELECT field1, field2, id FROM
> tblMigration WHERE field1='parent'
> 3. do remaing migration relating the tblParent.old_id to tblMIgration.id
> field
> 4. and then drop the old_id collumn from tblParent
Either way, if you have a natural key in the field1 or field2 combination
that you can use for getting the surrogate (identity) key that is even
better.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:1A0B0471-24CF-414D-AE0B-5EF830993828@.microsoft.com...
> ...using SQL 2K
> ...This is for a data migration project which involves the splitting of a
> flat file into two or more tables with relationships. The migration will
> happen over time and not at one time.
> ...My SQL Server/DB experience up to this point has been primarily focused
> on client side request, but am pretty short on optimization... so I need
> some
> direction here. I can research via Google, BOL and forums, but I don't
> even
> know where to start.
> I am currently using a cursor to walk through rows in tblMigration and
> build
> an insert statement for tblParent. I am using the cursor becuase I need
> to
> capture the new identity value (SCOPE_IDENTITY) after each insert into
> tblParent and use it to migrate the rest of the records destined for the
> various child tables in the new schema.
> I am moving data from one server to another and have set up a linked
> server.
> currently a migration of 53,000 records is taking 42 min (on my dev box:
> WINXP, 1GB ram, 2 GHZ).
> I originally wanted to do the following, but was told this would play
> havoc
> with replication
> 1. ALTER tblParent ADD old_id BIGINT
> 2. INSERT tblParent (field1,field2,old_id) SELECT field1, field2, id FROM
> tblMigration WHERE field1='parent'
> 3. do remaing migration relating the tblParent.old_id to tblMIgration.id
> field
> 4. and then drop the old_id collumn from tblParent
> Once again, I have not posted actual TSQL because it would be very long
> and
> I just need the pointer in the right direction... but if that would help,
> let
> me know.
> thanks
> Kevin
>|||"Louis Davidson" wrote:

> Havoc with replication? How?
>
I was told, by our SQL DBA, that ad-hoc schema changes would not be allowed
with replication. Now I am not certain if he meant
1. that replication, by design, would cause any schema changes to fail
2. that schema changes would be in violation of business rules because they
would break replication and thus I was not to do this or
3. that replication would destroy any schema changes I made by overwritting
them
I am working on the understanding, a bit fuzzy as it is, that replication
essentially is a manage procedure for copying database schema and data
changes from one server to another as a form of backup and/or security.
Suffice it to say, I was explicity told "No schema changes". Personnaly I
don't see the big deal wth adding collumns as this will be invisible to any
client apps unless they are calculating the ordinal position of particular
collumns... which would be pretty insane.
In either case saying that a migration of a midsized client would take 40
minutes got a consession. I will throwing my old_id into an existing collum
n
that would never be populated by legacy records on the most critical part of
the migration.
I will still have to use cursors for some of the smaller tables.
If there is a way to optimize (hints etc), I would love the input.
thanks.
Kevin
"Louis Davidson" wrote:

> Havoc with replication? How?
>

Optimization

In db maintenance when you schedule optimizations, in addition to rebuilding
all the indexes does sql recompile all tables as well, which in turn should
recompile all stored procs accessing those tables to use the latest and most
efficient query plan? Thanks.
Hi
SQL does not recompile the tables, there is no such functionality.
During a Index re-build, the statistics are updated if the setting is on for
the DB.
SP's are not automaticlly re-compiled. Unless you set your SP to use "with
recompile", (which is not optimal as this would happen everytime the SP gets
executed), the SP will only get re-compiled when SQL Server's criteria are
met (aged out of procedure cache or the self tuning detects that the
statistics have changed enough).
Regards
Mike
"mrdj" wrote:

> In db maintenance when you schedule optimizations, in addition to rebuilding
> all the indexes does sql recompile all tables as well, which in turn should
> recompile all stored procs accessing those tables to use the latest and most
> efficient query plan? Thanks.
|||I believe Mike meant
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:DE531A23-A31B-4EB2-9C7D-492C6656762A@.microsoft.com...
> Hi
> SQL does not recompile the tables, there is no such functionality.
> During a Index re-build, the statistics are updated if the setting is on
for
> the DB.
> SP's are not automaticlly re-compiled. Unless you set your SP to use "with
> recompile", (which is not optimal as this would happen everytime the SP
gets[vbcol=seagreen]
> executed), the SP will only get re-compiled when SQL Server's criteria are
> met (aged out of procedure cache or the self tuning detects that the
> statistics have changed enough).
> Regards
> Mike
>
> "mrdj" wrote:
rebuilding[vbcol=seagreen]
should[vbcol=seagreen]
most[vbcol=seagreen]