Field grouping in Report Builder

Hi,

I build all my report with the report builder.

When I build a report that the first column is ID and I've several records with the same ID one after abnother the report is grouping the ID data (see 2 in the example below)

How can I cancel the grouping for report or for the entire environment?

example:

ID Date

1 1/1/1990

2 1/1/1990

1/1/2000

3 1/1/2005

Dear ,

Go to the data tab and remove the grouping.

from

sufian

|||

Hi,

I'm building the report in Report Builder not report designer.

There is no data tab in the report builder.

Anyone?

|||

You need to make sure you have an entity group, not a value group (i.e. grouping on a field). Check the label on the gray "tab" above the fields in the Report Builder design area to see what you are grouping by. If dragging in MyEntity.ID didn't give you an entity group on MyEntity, then you probably need to set MyEntity.DiscourageGrouping=True in your report model. Regardless, you can be sure to get an entity group if you actually drag the entity onto your report instead of a field.

Once you have an entity group, make sure you add any other fields to the same group. Usually they will go in by default, but if you specifically drop them on the far left edge, you will get a new group on that field.

Hope this helps!

Fetching Record From Table

Hi,
I am facing a peculier problem. I have 3 records in my table. when i am trying to fetch the top 2 its able to fetch the records. but when trying fro moire than 2 (say top 3 or just the *) it say time out. can any one help me out in this regard.

Below is the error that I get when tryong to open the record from SSMS

SQL Execution Error.

Executed SQL statement: SELECT Transaction_ID, tril_gid, WorkGroup_ID, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate, LCID FROM Symp_TransactionHeader
Error Source: .Net SqlClient Data Provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
--------
OK Help
--------

Thanks,
Rahul Jhawhere's the TOP in that query?|||there is no top in the query......... the system wil generate the query when you try to open the table from ssms...........

Fetch Returning Duplicate Last Record

Ok, this thing is returning the last record twice. If I have only one record it returns it twice, multiple records gives me the last one twice. I am sure some dumb pilot error is involved, HELP!

Thanks in advance, Larry

ALTER FUNCTION dbo.TestFoodDisLikes

(

@.ResidentID int

)

RETURNS varchar(250)

AS

BEGIN

DECLARE @.RDLike varchar(50)

DECLARE @.RDLikeList varchar(250)

BEGIN

SELECT @.RDLikeList = ''

DECLARE RDLike_cursor CURSOR

LOCAL SCROLL STATIC

FOR

SELECT FoodItem

FROM tblFoodDislikes

WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')

OPEN RDLike_cursor

FETCH NEXT FROM RDLike_cursor

INTO @.RDLike

SELECT @.RDLikeList = @.RDLike

WHILE @.@.FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM RDLike_cursor

INTO @.RDLike

SELECT @.RDLikeList = @.RDLikeList + ', ' + @.RDLike

END

CLOSE RDLike_cursor

DEALLOCATE RDLike_cursor

END

RETURN @.RDLikeList

END

Your selecting into the cursor before any operation in the begin statement. ****

ALTER FUNCTION dbo.TestFoodDisLikes
(
@.ResidentID int
)
RETURNS varchar(250)
AS
BEGIN
DECLARE @.RDLike varchar(50)
DECLARE @.RDLikeList varchar(250)
BEGIN
SELECT @.RDLikeList = ''
DECLARE RDLike_cursor CURSOR
LOCAL SCROLL STATIC
FOR
SELECT FoodItem
FROM tblFoodDislikes
WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')
OPEN RDLike_cursor
FETCH NEXT FROM RDLike_cursor
INTO @.RDLike
SELECT @.RDLikeList = @.RDLike
WHILE @.@.FETCH_STATUS = 0
BEGIN --****

SELECT @.RDLikeList = @.RDLikeList + ', ' + @.RDLike--****
FETCH NEXT FROM RDLike_cursor INTO @.RDLike--****

END--****
CLOSE RDLike_cursor
DEALLOCATE RDLike_cursor
END
RETURN @.RDLikeList
END

|||

Steve,

It still duplicates one record, that just moved it to the beginning. If I am expecting to see somethimg like 'Ham, Beans, Biscuit, Apples, Beets' I get 'Ham, Ham, Beans, Biscuit, Apples, Beets'. Looks like the second part of the FETCH is starting all over again

|||Actually I would avoid using a cursor for this, according to the Northwind database it would be something like this:

DECLARE @.Cities VARCHAR(8000)

SET @.Cities = ''

SELECT @.Cities = CASE @.Cities

WHEN '' THEN City

ELSE @.Cities + ', ' + City

END

from CUstomers

Group by City

Select @.Cities

According to your problem it would be

DECLARE @.ResidentID VARCHAR(8000)

DECLARE @.FoodItems VARCHAR(8000)

SET @.FoodItems = ''

SELECT @.FoodItems =CASE @.FoodItems

WHEN '' THEN FoodItem

ELSE @.FoodItems + ', ' + FoodItem

END

from tblFoodDislikes

WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')

Group by FoodItem

Select @.FoodItems

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens, That works great and I did use it, seems faster. I am still at a loss as to why the fetch was returning a duplicate record but I like this better.

Larry

fastest way to deduplicate a list

Im trying to dedupe a table with only one field on it. The table has
40 million records in it. What is the fastest way?

1) create a table with a unque constraint on it insert into that
table?

2) create a table without a unique constraint on it and use insert
into table select distinct un from table2?

3) another way?

MichaelMichael Evanchik (mre224@.yahoo.com) writes:

Quote:

Originally Posted by

Im trying to dedupe a table with only one field on it. The table has
40 million records in it. What is the fastest way?
>
1) create a table with a unque constraint on it insert into that
table?

I assume that you would use the IGNORE_DUP_KEY option? Else the scheme
wouldn't work. That could very well be the fastest method.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Fastest way of updating a row

In relation to my last post, I have a question for the SQL-gurus.

I need to update 70k records, and mark all those updated in a special
column for further processing by another system.

So, if the record was

Key1, foo, foo, ""

it needs to become

Key1, fap, fap, "U"

iff and only iff the datavalues are actually different (as above, foo
becomes fap),

otherwise it must become

Key1, foo,foo, ""

Is it quicker to :
1) get the row of the destination table, inspect all values
programatically, and determine IF an update query is needed

OR

2) just do a update on all rows, but adding
and (field1 <> value1 or field2<>value2) to the update query

that is
update myTable
set
field1 = "foo"
markField="u"
where key="mykey" and (field1 <> foo)

The first one will not generate new update queries if the record has
not changed, on account of doing a select, whereas the second version
always runs an update, but some of them will not affect any lines.

Will I need a full index on the second version?

Thanks in advance,
Asger Henriksen[posted and mailed, vnligen svara i nys]

Asger Jensen (akj@.tmnet.dk) writes:
> Is it quicker to :
> 1) get the row of the destination table, inspect all values
> programatically, and determine IF an update query is needed
> OR
> 2) just do a update on all rows, but adding
> and (field1 <> value1 or field2<>value2) to the update query
> that is
> update myTable
> set
> field1 = "foo"
> markField="u"
> where key="mykey" and (field1 <> foo)

I'm not sure that I follow, but it sounds to me that the in first
approach you would retrieve rows one by one.

In any case, the second approach leaves all the jub to the computer,
and there is a reason why we have computers, isn't there? :-)

The only catch is that with too many rows in the table there can be
a strain on the transaction log. But with only 70000 rows, this is
not worth worrying about.

Obviously there query will run faster if there is a clustered index
on the column "key".

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> I'm not sure that I follow, but it sounds to me that the in first
> approach you would retrieve rows one by one.

Yes, thats what it was. Nasty.
> In any case, the second approach leaves all the jub to the computer,
> and there is a reason why we have computers, isn't there? :-)
:-), yes, only in my scenario it took soooo long.

> The only catch is that with too many rows in the table there can be
> a strain on the transaction log. But with only 70000 rows, this is
> not worth worrying about.

ok,

> Obviously there query will run faster if there is a clustered index
> on the column "key".
There was, actually, but during the 70000 records it became slower and
slower. I solved it by adding a dynamically generated index on ALL
fields, not just "key", this sped things up considerably, so I went
from 1 hour to 10 minutes.

I guess it is due to the server needing to do a lookup/record on all
value fields to see if they have changed, and this can be done more
efficiently with a n index.

Regards
Asger

Fastest method for Inserting 1 million records into SQL Database

I am reading a text file and modifing the data to match fields in a SQL 2000 Database then inserting the record in. I am using vb.net and have tried various methods but all are to slow. I would appreciate any help anybody could offer.
Have you tried BCP or BULK INSERT? It sounds like you're inserting the data
row-by-row using VB.NET; perhaps you can get away with a BULK INSERT using a
format file to tweak the data? It would probably also be faster if you BULK
INSERT the data in its "raw" form into a temporary table and then move it to
its final destination using INSERT ... SELECT, and do any data modifications
necessary in the SELECT.
"BradC" <BradC@.discussions.microsoft.com> wrote in message
news:F2AA271E-A8B7-448F-83BC-900EFFB679E8@.microsoft.com...
> I am reading a text file and modifing the data to match fields in a SQL
2000 Database then inserting the record in. I am using vb.net and have tried
various methods but all are to slow. I would appreciate any help anybody
could offer.

Fastest method for Inserting 1 million records into SQL Database

Adam is totally correct, however you get best results by
putting the file on the Server that your SQL Server is
on, that way there isn't going to be a network overhead.
J

>--Original Message--
>I am reading a text file and modifing the data to match
fields in a SQL 2000 Database then inserting the record
in. I am using vb.net and have tried various methods but
all are to slow. I would appreciate any help anybody
could offer.
>.
>
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:2d85901c469da$da1d8050$a301280a@.phx.gbl...
> ..and I hope people don't take that as arrogantly as it
> sounds...
The part about network overhead? Yeah, I thought you went way over the
line on that one
Actually, I have no clue what you're talking about!

Fastest method for Inserting 1 million records into SQL Database

Adam is totally correct, however you get best results by
putting the file on the Server that your SQL Server is
on, that way there isn't going to be a network overhead.
J

>--Original Message--
>I am reading a text file and modifing the data to match
fields in a SQL 2000 Database then inserting the record
in. I am using vb.net and have tried various methods but
all are to slow. I would appreciate any help anybody
could offer.
>.
>"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:2d85901c469da$da1d8050$a301280a@.phx
.gbl...
> ..and I hope people don't take that as arrogantly as it
> sounds...
The part about network overhead? Yeah, I thought you went way over the
line on that one
Actually, I have no clue what you're talking about!

Fastest method for Inserting 1 million records into SQL Database

I am reading a text file and modifing the data to match fields in a SQL 2000
Database then inserting the record in. I am using vb.net and have tried var
ious methods but all are to slow. I would appreciate any help anybody could
offer.Have you tried BCP or BULK INSERT? It sounds like you're inserting the data
row-by-row using VB.NET; perhaps you can get away with a BULK INSERT using a
format file to tweak the data? It would probably also be faster if you BULK
INSERT the data in its "raw" form into a temporary table and then move it to
its final destination using INSERT ... SELECT, and do any data modifications
necessary in the SELECT.
"BradC" <BradC@.discussions.microsoft.com> wrote in message
news:F2AA271E-A8B7-448F-83BC-900EFFB679E8@.microsoft.com...
> I am reading a text file and modifing the data to match fields in a SQL
2000 Database then inserting the record in. I am using vb.net and have tried
various methods but all are to slow. I would appreciate any help anybody
could offer.

faster query - smaller tables?

My database is much faster now because I found that I only need around
150,000 records in a table for any single query. The date selection will
always be in a certain range.
So if I break my tables up into smaller tables based on the maximum date
range, its much faster.
I don't understand why its 2x's slower if there are more than x-number of
records in the table.. I have tried non-clustered and then clustered indexes
on the date field, and the date + customer id as the key fields... but its
still slower as more records are added to the table. Is this normal?
SELECT DISTINCT A.CustID, B.Discount
FROM Sales A, Discounts B WHERE
A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount > 0.2
If I have 9.5 million records in Sales, the query takes 2 seconds to 4
seconds.
If I have about 60 tables of 150000 records each, and I select from the
appropriate table, the query is about a half second or less.
Is this a common practice, to break tables up based on date range or am I
still doing something wrong?
CREATE TABLE [Sales] (
[SaleDate] [datetime] NOT NULL ,
[CustID] [varchar] (10) NOT NULL ,
[SaleAmt] [DECIMAL (8,3)] NOT NULL
) ON [PRIMARY]
CREATE TABLE [Discounts] (
[DiscountDate] [datetime] NOT NULL ,
[Discount] [DECIMAL (8,3)] NOT NULL
[Code] [numeric] NOT NULL
) ON [PRIMARY]Rich
You may want to read 'Creating a Partitioned View' article in the BOL.
Does the optimizer available to use indexes defined on the table?
"Rich" <no@.spam.invalid> wrote in message
news:UekHe.54059$4o.18050@.fed1read06...
> My database is much faster now because I found that I only need around
> 150,000 records in a table for any single query. The date selection will
> always be in a certain range.
> So if I break my tables up into smaller tables based on the maximum date
> range, its much faster.
> I don't understand why its 2x's slower if there are more than x-number of
> records in the table.. I have tried non-clustered and then clustered
> indexes
> on the date field, and the date + customer id as the key fields... but its
> still slower as more records are added to the table. Is this normal?
> SELECT DISTINCT A.CustID, B.Discount
> FROM Sales A, Discounts B WHERE
> A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount >
> 0.2
> If I have 9.5 million records in Sales, the query takes 2 seconds to 4
> seconds.
> If I have about 60 tables of 150000 records each, and I select from the
> appropriate table, the query is about a half second or less.
> Is this a common practice, to break tables up based on date range or am I
> still doing something wrong?
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [SaleAmt] [DECIMAL (8,3)] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Discounts] (
> [DiscountDate] [datetime] NOT NULL ,
> [Discount] [DECIMAL (8,3)] NOT NULL
> [Code] [numeric] NOT NULL
> ) ON [PRIMARY]
>|||Hi
Where are your indexes?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" wrote:

> My database is much faster now because I found that I only need around
> 150,000 records in a table for any single query. The date selection will
> always be in a certain range.
> So if I break my tables up into smaller tables based on the maximum date
> range, its much faster.
> I don't understand why its 2x's slower if there are more than x-number of
> records in the table.. I have tried non-clustered and then clustered index
es
> on the date field, and the date + customer id as the key fields... but its
> still slower as more records are added to the table. Is this normal?
> SELECT DISTINCT A.CustID, B.Discount
> FROM Sales A, Discounts B WHERE
> A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount > 0
.2
> If I have 9.5 million records in Sales, the query takes 2 seconds to 4
> seconds.
> If I have about 60 tables of 150000 records each, and I select from the
> appropriate table, the query is about a half second or less.
> Is this a common practice, to break tables up based on date range or am I
> still doing something wrong?
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [SaleAmt] [DECIMAL (8,3)] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Discounts] (
> [DiscountDate] [datetime] NOT NULL ,
> [Discount] [DECIMAL (8,3)] NOT NULL
> [Code] [numeric] NOT NULL
> ) ON [PRIMARY]
>
>|||Dear Rich,
When processing a join, The SQL optimizer evaluates all reasonable join
permutations and estimates the total I/O cost, in terms of I/O time. The pla
n
resulting in the lowest estimate of I/O time is the plan chosen.
Please Note that As the number of tables increases, then the number of
permutations that the optimizer must evaluate increases as a factorial of th
e
number of tables in the query:
I.E: Nbr Of Tables is 2 then Nbr Of Permutions On SQL is 2!
Nbr Of Tables is 3 then Nbr Of Permutions On SQL is 3!
Nbr Of Tables is 4 then Nbr Of Permutions On SQL is 4!
and so on...
In Your case you have 2 tables and as i can see they are not related(No Join
In Between), one way to solve your problem and optimize your query processin
g
time is either you filter your tables Discounts and Sales and then build the
join,
Or you build a relation between both tables on primary indexes and then make
your criteria fields as clustered indexes(i.e: SaleDate , DiscountDate,
Discount)
i prefer you combine both ways
Good Luck
Mario Aoun
"Rich" wrote:

> My database is much faster now because I found that I only need around
> 150,000 records in a table for any single query. The date selection will
> always be in a certain range.
> So if I break my tables up into smaller tables based on the maximum date
> range, its much faster.
> I don't understand why its 2x's slower if there are more than x-number of
> records in the table.. I have tried non-clustered and then clustered index
es
> on the date field, and the date + customer id as the key fields... but its
> still slower as more records are added to the table. Is this normal?
> SELECT DISTINCT A.CustID, B.Discount
> FROM Sales A, Discounts B WHERE
> A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount > 0
.2
> If I have 9.5 million records in Sales, the query takes 2 seconds to 4
> seconds.
> If I have about 60 tables of 150000 records each, and I select from the
> appropriate table, the query is about a half second or less.
> Is this a common practice, to break tables up based on date range or am I
> still doing something wrong?
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [SaleAmt] [DECIMAL (8,3)] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Discounts] (
> [DiscountDate] [datetime] NOT NULL ,
> [Discount] [DECIMAL (8,3)] NOT NULL
> [Code] [numeric] NOT NULL
> ) ON [PRIMARY]
>
>|||I was just curious about what is the approximate criterion
which allows an optimizer to tell that
T3 joined T1 joined T2
is faster than
T1 joined T2 joined T3 ?
Is it based on the number of columns? Can't be based on
rows because their number is unknown before the join
and would take to much time to compute it...
Any information or pointer on this problem?
Thank you
- Pamela
.NET developer|||Rich,
Why aren't the two tables joined in the query? Do all Customers get all
Discounts? That doesn't seem to make sense (from a data model point of
view).
Assuming you do have some common key (and you join on it in the query),
then this type of query would benefit from a clustered index on the date
range, or from a covering index (see BOL for more details).
Hope this helps,
Gert-Jan
Rich wrote:
> My database is much faster now because I found that I only need around
> 150,000 records in a table for any single query. The date selection will
> always be in a certain range.
> So if I break my tables up into smaller tables based on the maximum date
> range, its much faster.
> I don't understand why its 2x's slower if there are more than x-number of
> records in the table.. I have tried non-clustered and then clustered index
es
> on the date field, and the date + customer id as the key fields... but its
> still slower as more records are added to the table. Is this normal?
> SELECT DISTINCT A.CustID, B.Discount
> FROM Sales A, Discounts B WHERE
> A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount > 0
.2
> If I have 9.5 million records in Sales, the query takes 2 seconds to 4
> seconds.
> If I have about 60 tables of 150000 records each, and I select from the
> appropriate table, the query is about a half second or less.
> Is this a common practice, to break tables up based on date range or am I
> still doing something wrong?
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [SaleAmt] [DECIMAL (8,3)] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Discounts] (
> [DiscountDate] [datetime] NOT NULL ,
> [Discount] [DECIMAL (8,3)] NOT NULL
> [Code] [numeric] NOT NULL
> ) ON [PRIMARY]|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42EE6C4F.DD1000E0@.toomuchspamalready.nl...
> Rich,
> Why aren't the two tables joined in the query? Do all Customers get all
> Discounts? That doesn't seem to make sense (from a data model point of
> view).
> Assuming you do have some common key (and you join on it in the query),
> then this type of query would benefit from a clustered index on the date
> range, or from a covering index (see BOL for more details).
> Hope this helps,
> Gert-Jan
That helps, thanks.
Richard
> Rich wrote:
of
indexes
its
0.2
I

fast, consistent way to bulk insert?

I'm looking for a way to insert 50k records into a SQL Server table, and need to get it done faster. right now using BULK INSERT takes 5-10 seconds, but faster would be better, and even better if it were a consistent amount of time.

I've heard of DTS but don't know quite how to use it - would be offer any performance gains? any clue what the bottleneck is for BULK INSERT? hard drive speed? amount of RAM (this was on a 512mb machine)? parsing the fields?

thanks for any ideas.512mb is rock bottom for running a production SQL box. Are you appending these records to an existing table? Is it something you're recreating every time? There's other options available if you're starting from scratch each go...|||...and as I understand it Bulk Insert is faster than anythting DTS can offer. BCP? I don't know about comparisons of BULK INSERT and BCP - maybe oters do.

Also you can try inserting into another table and then inserting from that. If you validate the data in the intermediary table you can use the NOCHECK option on constraints to speed up inserting.|||There's a comparison of bulk insert methods here. All the comparisons are for 2005 though.

http://weblogs.sqlteam.com/mladenp/archive/2006/07/17/10634.aspx|||What I have heard is that BULK INSERT is slightly faster than bcp mainly because bcp is a client side utility and BULK INSERT is internal to SQL Server. I could be wrong.|||Want faster imports?

Drop the indexes and use bcp|||thanks for the suggestions, I'll check them out. 512mb is definitely low, and will be upgraded once approved (ah bureaocracy)

this bulk insert will actually be done several times during the day, adding to the table instead of updating, and has to be able to be initiated from code (i.e. .NET app). looks like SSIS is something to check out.|||also, if you use bcp it can be considerably faster if you specify the TABLOCK hint in your query. You can pass the TABLOCK hint using the -h flag.

More tips here:

http://www.databasejournal.com/features/mssql/article.php/3095511

Fast Way to Concat Records to Comma Sep VARCHAR

Hi All,

Here's a challenge.

If there is a 'one word' answer to this, i.e. the name of a built in
SQL Server function I can use, that would be great! However...

I have a standard 1:m relationship, e.g. tblHeader and tblDetail.

I want to create a view of records from 'tblHeader' and within that
view have a column (called e.g. DetailTypes) that provides a single
comma separated varchar of values from a varchar field (called e.g.
DetailType) that appears in related records in the 'tblDetail' table.
(hope you understood that :)

e.g. the contents of my 'tblDetail' table could be...

Detail ID | Header ID | DetailType
-----------
1 | 1 | A
2 | 1 | A
3 | 1 | B
4 | 2 | A
5 | 2 | C
6 | 3 | B

Therefore, the view I want to create should return:

Header ID | DetailTypes
----------
1 | 2A, 1B
2 | 1A, 1C
3 | 1B

i.e. the first row can be read as "Header 1 has 2 'A' detail records
and 1 'B' detail record."

I have created a view e.g. 'vqryHeaders' which calls a user defined
function that takes the HeaderID and opens a cursor on another view,
e.g. 'vqryDetailGrouped'. The other view groups the records in
tblDetail so that I can get a count of each DetailType for each Header
ID. The cursor then loops through the returned records concatenating
the count and detail type into a comma separated string (as shown
above).

However, when I run this across 20k records it is soooo sloooooww. I
have indexes on the relationship fields and I am using realistically
sized varchars - neither made any difference in speed. It is
definately the function that I wrote that slows it down as the view is
lightning fast when I remove my function call.

I can supply source code if necessary, but I think that this is a
kind-of generic problem so I don't see the point - yet.

I really hope you can help.

Regards,

Jezz"Jeremy Pridmore" <google.news@.pridmorej.freeserve.co.uk> wrote in message
news:9bce5a3f.0401190823.26a1bfe8@.posting.google.c om...
> Hi All,
> Here's a challenge.
> If there is a 'one word' answer to this, i.e. the name of a built in
> SQL Server function I can use, that would be great! However...
> I have a standard 1:m relationship, e.g. tblHeader and tblDetail.
> I want to create a view of records from 'tblHeader' and within that
> view have a column (called e.g. DetailTypes) that provides a single
> comma separated varchar of values from a varchar field (called e.g.
> DetailType) that appears in related records in the 'tblDetail' table.
> (hope you understood that :)
> e.g. the contents of my 'tblDetail' table could be...
> Detail ID | Header ID | DetailType
> -----------
> 1 | 1 | A
> 2 | 1 | A
> 3 | 1 | B
> 4 | 2 | A
> 5 | 2 | C
> 6 | 3 | B
> Therefore, the view I want to create should return:
> Header ID | DetailTypes
> ----------
> 1 | 2A, 1B
> 2 | 1A, 1C
> 3 | 1B
>
> i.e. the first row can be read as "Header 1 has 2 'A' detail records
> and 1 'B' detail record."
> I have created a view e.g. 'vqryHeaders' which calls a user defined
> function that takes the HeaderID and opens a cursor on another view,
> e.g. 'vqryDetailGrouped'. The other view groups the records in
> tblDetail so that I can get a count of each DetailType for each Header
> ID. The cursor then loops through the returned records concatenating
> the count and detail type into a comma separated string (as shown
> above).
> However, when I run this across 20k records it is soooo sloooooww. I
> have indexes on the relationship fields and I am using realistically
> sized varchars - neither made any difference in speed. It is
> definately the function that I wrote that slows it down as the view is
> lightning fast when I remove my function call.
> I can supply source code if necessary, but I think that this is a
> kind-of generic problem so I don't see the point - yet.
> I really hope you can help.
> Regards,
> Jezz

The fastest (and easiest) way would probably be in a front end application,
as this sort of thing is very awkward in pure SQL. You might find these
links useful for more information:

http://www.aspfaq.com/show.asp?id=2279
http://tinyurl.com/bib2

Simon|||The fastest way I know is to use the TSQL extension to the update
statement.
UPDATE table
SET @.var = col = expression which manipulates col
WHERE condition

I won't go into the details because it's rather lengthy and my wife is
waiting for me... I've included sample code and the output. -- Louis

------------------------
create table #H (headerID smallint)
insert into #H values(1)
insert into #H values(2)
insert into #H values(3)

create table #D (detailID smallint,headerID smallint,detailType
char(1))
insert into #D values(1,1,'A')
insert into #D values(2,1,'A')
insert into #D values(3,1,'B')
insert into #D values(4,2,'A')
insert into #D values(5,2,'C')
insert into #D values(6,3,'B')

select
d.headerid,
cast(count(d.detailtype) as varchar)+cast(detailtype as varchar) as
detailtypes,
identity(int,1,1) as i
into #T
from #H as h
join #D as d
on h.headerid=d.headerid
group by d.headerid,d.detailtype
order by d.headerid,d.detailtype

select headerid,identity(int,1,1) as i
into #Cursor
from #T
group by headerid order by headerid

declare @.i int, @.text varchar(8000)
set @.i=1
while exists(select * from #cursor where i=@.i) begin

set @.text=''
update #T
set @.text=detailtypes=@.text +',' + detailtypes
where headerid=(select headerid from #cursor where i=@.i)

set @.i=@.i+1
end

select a.headerid,right(detailtypes,len(detailtypes)-1) as detailtypes
from #T as a
join
(
select headerid,max(i) as i from #T group by headerid
) as b
on a.i=b.i

OUTPUT
headerid detailtypes
--- ------------------
1 2A,1B
2 1A,1C
3 1B|||>> I think that this is a kind-of generic problem .. <<

It is generic in the sense that newbies who don't understand what
First Normal Form (1NF) mean keep posting for a way to do front end
displays and reports in the SQL backend. This comes from not having
worked with a client/server architecture before, so you want to see
everything for the app written in one monolithic program.

The only safe way is to use a cursor and reutrn to slow, procedural
programming instead of declarative SQL programming. Any kludge you
use will not port to another SQL product, will run like glue and will
be almost unpredictable.|||joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0401191731.47560540@.posting.google.com>...
> >> I think that this is a kind-of generic problem .. <<
> It is generic in the sense that newbies who don't understand what
> First Normal Form (1NF) mean keep posting for a way to do front end
> displays and reports in the SQL backend. This comes from not having
> worked with a client/server architecture before, so you want to see
> everything for the app written in one monolithic program.
> The only safe way is to use a cursor and reutrn to slow, procedural
> programming instead of declarative SQL programming. Any kludge you
> use will not port to another SQL product, will run like glue and will
> be almost unpredictable.

Thanks for confirming that the world still has some complete w@.nkers
in it.

1. Don't make assumptions. I'm not a newbie - as my post said I
already have an answer, albeit a slow one.
2. Don't make assumptions. I have worked on several successful C/S
apps.
3. Don't make assumptions. I don't necessarily want my app complied
into one large monolithic program. Also, If it is possible to create
a reusable function into which you can pass several parameters and get
a concatenated string, that procedure would be classed as 'generic'.
4. Don't make assumptions. I don't want it to port to any other
platform. Why do you think I posted in comp.databases.ms-sqlserver
and not in comp.databases?

I already have code that runs like glue, which is why I posted. I
would expect any *intelligent* person to give a reasonable response
(such as Louis) - not a rant.

Although an appropriate procedure can be created in 'front-end' code -
in my case i'm using vbscript (more fuel?), I would expect that
varchar 'lookup' and manipulation in the database would be quicker
than having to retrieve all the data into the front end and process it
there.

Oh, and lets not forget the most inportant thing - the reason why I
want to do this! It will give me a searchable field in a view to
which I can apply my 'generic' filtering procedures.

So, how is your snow coming along?

"You're not much use to me alive are you" CELKO?
- Bricktop.

La, la, la - I'm ignoring you now.

What was that? Whatever.|||louisducnguyen@.hotmail.com (louis nguyen) wrote in message news:<b0e9d53.0401191541.34c5bf22@.posting.google.com>...
> The fastest way I know is to use the TSQL extension to the update
> statement.
> UPDATE table
> SET @.var = col = expression which manipulates col
> WHERE condition
> I won't go into the details because it's rather lengthy and my wife is
> waiting for me... I've included sample code and the output. -- Louis
<snip
Thanks Louis - you reminded me about temporary tables. That will go
along way in helping me - my problem probably lays with the fact that
I want to return this concatenated field in a view - not in a stored
proc, so the only way i can think of doing it is to use a function
that it aliased and pass in the primary key, select the records i need
in the function and then concatenate them.

Thanks again,

Jeremy|||Jeremy Pridmore (google.news@.pridmorej.freeserve.co.uk) writes:
> louisducnguyen@.hotmail.com (louis nguyen) wrote in message
news:<b0e9d53.0401191541.34c5bf22@.posting.google.com>...
>> The fastest way I know is to use the TSQL extension to the update
>> statement.
>> UPDATE table
>> SET @.var = col = expression which manipulates col
>> WHERE condition
>>
>> I won't go into the details because it's rather lengthy and my wife is
>> waiting for me... I've included sample code and the output. -- Louis
>>
><snip>
> Thanks Louis - you reminded me about temporary tables. That will go
> along way in helping me - my problem probably lays with the fact that
> I want to return this concatenated field in a view - not in a stored
> proc, so the only way i can think of doing it is to use a function
> that it aliased and pass in the primary key, select the records i need
> in the function and then concatenate them.

Beware that any "fast" solution to your problem will rely on
undocumented and undefined behaviour. The only safe way to this
in SQL is to it with a cursor or some other iterative method.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> The fastest way I know is to use the TSQL extension to the update
>> statement.
>> UPDATE table
>> SET @.var = col = expression which manipulates col
>> WHERE condition
>
Erland:
>Beware that any "fast" solution to your problem will rely on
>undocumented and undefined behaviour. The only safe way to this
>in SQL is to it with a cursor or some other iterative method.

Erland - are you referring to the code above here? What do you mean
exactly? Is there some sneakiness of undocumented goodness you are
keeping from us?|||I'll admit the code I posted is not standard sql. The extension to
the UPDATE statement is written about in Inside SQL Server 7.0 in the
brain teasers section. It was used to calculate running totals.|||WangKhar (Wangkhar@.yahoo.com) writes:
> Erland - are you referring to the code above here? What do you mean
> exactly? Is there some sneakiness of undocumented goodness you are
> keeping from us?

No, the problem is that you have no guarantee that the code will always
produce the expected result. A change of query plan or whatever. Could
break with the next service pack.

Here is a KB article on a similar trick. Pay particular attention to
first paragraph under CAUSE:
http://support.microsoft.com/default.aspx?scid=287515.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thats interesting, thanks.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94786FEB7DBEYazorman@.127.0.0.1>...
> WangKhar (Wangkhar@.yahoo.com) writes:
> > Erland - are you referring to the code above here? What do you mean
> > exactly? Is there some sneakiness of undocumented goodness you are
> > keeping from us?
> No, the problem is that you have no guarantee that the code will always
> produce the expected result. A change of query plan or whatever. Could
> break with the next service pack.
> Here is a KB article on a similar trick. Pay particular attention to
> first paragraph under CAUSE:
> http://support.microsoft.com/default.aspx?scid=287515.|||Does this help:
http://www.sqlteam.com/item.asp?ItemID=2368

"Jeremy Pridmore" <google.news@.pridmorej.freeserve.co.uk> wrote in message
news:9bce5a3f.0401190823.26a1bfe8@.posting.google.c om...
> Hi All,
> Here's a challenge.
> If there is a 'one word' answer to this, i.e. the name of a built in
> SQL Server function I can use, that would be great! However...
> I have a standard 1:m relationship, e.g. tblHeader and tblDetail.
> I want to create a view of records from 'tblHeader' and within that
> view have a column (called e.g. DetailTypes) that provides a single
> comma separated varchar of values from a varchar field (called e.g.
> DetailType) that appears in related records in the 'tblDetail' table.
> (hope you understood that :)
> e.g. the contents of my 'tblDetail' table could be...
> Detail ID | Header ID | DetailType
> -----------
> 1 | 1 | A
> 2 | 1 | A
> 3 | 1 | B
> 4 | 2 | A
> 5 | 2 | C
> 6 | 3 | B
> Therefore, the view I want to create should return:
> Header ID | DetailTypes
> ----------
> 1 | 2A, 1B
> 2 | 1A, 1C
> 3 | 1B
>
> i.e. the first row can be read as "Header 1 has 2 'A' detail records
> and 1 'B' detail record."
> I have created a view e.g. 'vqryHeaders' which calls a user defined
> function that takes the HeaderID and opens a cursor on another view,
> e.g. 'vqryDetailGrouped'. The other view groups the records in
> tblDetail so that I can get a count of each DetailType for each Header
> ID. The cursor then loops through the returned records concatenating
> the count and detail type into a comma separated string (as shown
> above).
> However, when I run this across 20k records it is soooo sloooooww. I
> have indexes on the relationship fields and I am using realistically
> sized varchars - neither made any difference in speed. It is
> definately the function that I wrote that slows it down as the view is
> lightning fast when I remove my function call.
> I can supply source code if necessary, but I think that this is a
> kind-of generic problem so I don't see the point - yet.
> I really hope you can help.
> Regards,
> Jezz

Fast Way To "Insert Into" a million records?

See the SQL below, on our SQL server this takes about
10min for 50,000 records, and about 3 hours for a million
records. Is there ANYTHING I can do to speed this up?
-Can I allocate DB space ahead of time?
-Can I put a table in to some type of lock mode'
-Is there something better than insert into?
INSERT INTO MaintHist (DebtorID, AssignCollector,
ChangeCollector, DateChanged, TableName, FieldName,
OldValue, NewValue)
SELECT TempUpdate.RecordUniqueValue, CAST
(TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
WHERE TempUpdate.BatchID = 505
Thanks!
Jasonyou can try to Bulk copy it in as this is non-logged. However that may NOT
be the right solution for you.
it's also fairly common to drop indexes before you do huge inserts and then
rebuild them when inserts are complete.
just food for thought
Greg Jackson
PDX, Oregon|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jason Roozee" <jason@.camcoinc.net> wrote in message
news:16e3c01c448b6$d7c55ca0$a601280a@.phx
.gbl...
> See the SQL below, on our SQL server this takes about
> 10min for 50,000 records, and about 3 hours for a million
> records. Is there ANYTHING I can do to speed this up?
>
Note that your example indicates an insert speed of 5000 records/minute.
That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
better than linear scaling. This is good.

> -Can I allocate DB space ahead of time?
Yes. Expand the database before adding the records. This will improve
performance some.
> -Can I put a table in to some type of lock mode'
SQL handles this automagically. I doubt you could improve performance with
a locking hint.
> -Is there something better than insert into?
You could try a DTS package to do the transfer, but I am not sure if that
will help.
There is always faster hardware.
>
> INSERT INTO MaintHist (DebtorID, AssignCollector,
> ChangeCollector, DateChanged, TableName, FieldName,
> OldValue, NewValue)
> SELECT TempUpdate.RecordUniqueValue, CAST
> (TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
> GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
> TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
> WHERE TempUpdate.BatchID = 505
> Thanks!
> Jason|||How would a BULK COPY be done?
Jason Roozee

>--Original Message--
>you can try to Bulk copy it in as this is non-logged.
However that may NOT
>be the right solution for you.
>it's also fairly common to drop indexes before you do
huge inserts and then
>rebuild them when inserts are complete.
>
>just food for thought
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||To add some to Geoff's comments. You might try inserting them in smaller
batches if at all possible. When inserting into an existing table it is
usually faster to do insert them in batches of say 10,000 rows vs all 3
million in one transaction. If you have a clustered index on the table
being inserted into you should try to insert them in that order as well.
Andrew J. Kelly
SQL Server MVP
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O7ZfMlLSEHA.3056@.TK2MSFTNGP11.phx.gbl...
> Comments Inline
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jason Roozee" <jason@.camcoinc.net> wrote in message
> news:16e3c01c448b6$d7c55ca0$a601280a@.phx
.gbl...
> Note that your example indicates an insert speed of 5000 records/minute.
> That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
> better than linear scaling. This is good.
>
> Yes. Expand the database before adding the records. This will improve
> performance some.
> SQL handles this automagically. I doubt you could improve performance
with
> a locking hint.
> You could try a DTS package to do the transfer, but I am not sure if that
> will help.
> There is always faster hardware.
>|||look up BCP in Books On Line for all the details.
Commonly used utility for blasting lots of data into sql server.
GAJ|||Im already using Bulk Insert to get the new data in to the
server, but now I need to update data from one table to
another table.
Jsason
>--Original Message--
>look up BCP in Books On Line for all the details.
>Commonly used utility for blasting lots of data into sql
server.
>
>GAJ
>
>.
>|||to do massive updates, you'll probably want to batch the updates into groups
as others have suggested.
Cheers,
GAJ

Fast Way To "Insert Into" a million records?

See the SQL below, on our SQL server this takes about
10min for 50,000 records, and about 3 hours for a million
records. Is there ANYTHING I can do to speed this up?
-Can I allocate DB space ahead of time?
-Can I put a table in to some type of lock mode?
-Is there something better than insert into?
INSERT INTO MaintHist (DebtorID, AssignCollector,
ChangeCollector, DateChanged, TableName, FieldName,
OldValue, NewValue)
SELECT TempUpdate.RecordUniqueValue, CAST
(TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
WHERE TempUpdate.BatchID = 505
Thanks!
Jason
you can try to Bulk copy it in as this is non-logged. However that may NOT
be the right solution for you.
it's also fairly common to drop indexes before you do huge inserts and then
rebuild them when inserts are complete.
just food for thought
Greg Jackson
PDX, Oregon
|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jason Roozee" <jason@.camcoinc.net> wrote in message
news:16e3c01c448b6$d7c55ca0$a601280a@.phx.gbl...
> See the SQL below, on our SQL server this takes about
> 10min for 50,000 records, and about 3 hours for a million
> records. Is there ANYTHING I can do to speed this up?
>
Note that your example indicates an insert speed of 5000 records/minute.
That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
better than linear scaling. This is good.

> -Can I allocate DB space ahead of time?
Yes. Expand the database before adding the records. This will improve
performance some.
> -Can I put a table in to some type of lock mode?
SQL handles this automagically. I doubt you could improve performance with
a locking hint.
> -Is there something better than insert into?
You could try a DTS package to do the transfer, but I am not sure if that
will help.
There is always faster hardware.
>
> INSERT INTO MaintHist (DebtorID, AssignCollector,
> ChangeCollector, DateChanged, TableName, FieldName,
> OldValue, NewValue)
> SELECT TempUpdate.RecordUniqueValue, CAST
> (TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
> GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
> TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
> WHERE TempUpdate.BatchID = 505
> Thanks!
> Jason
|||How would a BULK COPY be done?
Jason Roozee

>--Original Message--
>you can try to Bulk copy it in as this is non-logged.
However that may NOT
>be the right solution for you.
>it's also fairly common to drop indexes before you do
huge inserts and then
>rebuild them when inserts are complete.
>
>just food for thought
>
>Greg Jackson
>PDX, Oregon
>
>.
>
|||To add some to Geoff's comments. You might try inserting them in smaller
batches if at all possible. When inserting into an existing table it is
usually faster to do insert them in batches of say 10,000 rows vs all 3
million in one transaction. If you have a clustered index on the table
being inserted into you should try to insert them in that order as well.
Andrew J. Kelly
SQL Server MVP
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O7ZfMlLSEHA.3056@.TK2MSFTNGP11.phx.gbl...
> Comments Inline
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jason Roozee" <jason@.camcoinc.net> wrote in message
> news:16e3c01c448b6$d7c55ca0$a601280a@.phx.gbl...
> Note that your example indicates an insert speed of 5000 records/minute.
> That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
> better than linear scaling. This is good.
> Yes. Expand the database before adding the records. This will improve
> performance some.
> SQL handles this automagically. I doubt you could improve performance
with
> a locking hint.
> You could try a DTS package to do the transfer, but I am not sure if that
> will help.
> There is always faster hardware.
>
|||look up BCP in Books On Line for all the details.
Commonly used utility for blasting lots of data into sql server.
GAJ
|||Im already using Bulk Insert to get the new data in to the
server, but now I need to update data from one table to
another table.
Jsason
>--Original Message--
>look up BCP in Books On Line for all the details.
>Commonly used utility for blasting lots of data into sql
server.
>
>GAJ
>
>.
>
|||to do massive updates, you'll probably want to batch the updates into groups
as others have suggested.
Cheers,
GAJ

Fast Way To "Insert Into" a million records?

See the SQL below, on our SQL server this takes about
10min for 50,000 records, and about 3 hours for a million
records. Is there ANYTHING I can do to speed this up?
-Can I allocate DB space ahead of time?
-Can I put a table in to some type of lock mode'
-Is there something better than insert into?
INSERT INTO MaintHist (DebtorID, AssignCollector,
ChangeCollector, DateChanged, TableName, FieldName,
OldValue, NewValue)
SELECT TempUpdate.RecordUniqueValue, CAST
(TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
WHERE TempUpdate.BatchID = 505
Thanks!
Jasonyou can try to Bulk copy it in as this is non-logged. However that may NOT
be the right solution for you.
it's also fairly common to drop indexes before you do huge inserts and then
rebuild them when inserts are complete.
just food for thought
Greg Jackson
PDX, Oregon|||Comments Inline
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jason Roozee" <jason@.camcoinc.net> wrote in message
news:16e3c01c448b6$d7c55ca0$a601280a@.phx.gbl...
> See the SQL below, on our SQL server this takes about
> 10min for 50,000 records, and about 3 hours for a million
> records. Is there ANYTHING I can do to speed this up?
>
Note that your example indicates an insert speed of 5000 records/minute.
That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
better than linear scaling. This is good.
> -Can I allocate DB space ahead of time?
Yes. Expand the database before adding the records. This will improve
performance some.
> -Can I put a table in to some type of lock mode'
SQL handles this automagically. I doubt you could improve performance with
a locking hint.
> -Is there something better than insert into?
You could try a DTS package to do the transfer, but I am not sure if that
will help.
There is always faster hardware. :)
>
> INSERT INTO MaintHist (DebtorID, AssignCollector,
> ChangeCollector, DateChanged, TableName, FieldName,
> OldValue, NewValue)
> SELECT TempUpdate.RecordUniqueValue, CAST
> (TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
> GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
> TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
> WHERE TempUpdate.BatchID = 505
> Thanks!
> Jason|||How would a BULK COPY be done?
Jason Roozee
>--Original Message--
>you can try to Bulk copy it in as this is non-logged.
However that may NOT
>be the right solution for you.
>it's also fairly common to drop indexes before you do
huge inserts and then
>rebuild them when inserts are complete.
>
>just food for thought
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||To add some to Geoff's comments. You might try inserting them in smaller
batches if at all possible. When inserting into an existing table it is
usually faster to do insert them in batches of say 10,000 rows vs all 3
million in one transaction. If you have a clustered index on the table
being inserted into you should try to insert them in that order as well.
--
Andrew J. Kelly
SQL Server MVP
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O7ZfMlLSEHA.3056@.TK2MSFTNGP11.phx.gbl...
> Comments Inline
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jason Roozee" <jason@.camcoinc.net> wrote in message
> news:16e3c01c448b6$d7c55ca0$a601280a@.phx.gbl...
> > See the SQL below, on our SQL server this takes about
> > 10min for 50,000 records, and about 3 hours for a million
> > records. Is there ANYTHING I can do to speed this up?
> >
> Note that your example indicates an insert speed of 5000 records/minute.
> That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
> better than linear scaling. This is good.
> > -Can I allocate DB space ahead of time?
> Yes. Expand the database before adding the records. This will improve
> performance some.
> > -Can I put a table in to some type of lock mode'
> SQL handles this automagically. I doubt you could improve performance
with
> a locking hint.
> > -Is there something better than insert into?
> You could try a DTS package to do the transfer, but I am not sure if that
> will help.
> There is always faster hardware. :)
> >
> >
> >
> > INSERT INTO MaintHist (DebtorID, AssignCollector,
> > ChangeCollector, DateChanged, TableName, FieldName,
> > OldValue, NewValue)
> >
> > SELECT TempUpdate.RecordUniqueValue, CAST
> > (TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
> > GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
> > TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
> > WHERE TempUpdate.BatchID = 505
> >
> > Thanks!
> > Jason
>|||look up BCP in Books On Line for all the details.
Commonly used utility for blasting lots of data into sql server.
GAJ|||Im already using Bulk Insert to get the new data in to the
server, but now I need to update data from one table to
another table.
Jsason
>--Original Message--
>look up BCP in Books On Line for all the details.
>Commonly used utility for blasting lots of data into sql
server.
>
>GAJ
>
>.
>|||to do massive updates, you'll probably want to batch the updates into groups
as others have suggested.
Cheers,
GAJ

Fast Sequencing

I have a table that I am trying to bulk load 100K records via SQL. The table
has a primary key of ID and SubID. I am creating a temporary table that will
populate ID just fine, but is there a fast way I can run through these
records and assign the SubID. I have no problem doing this in VB (it runs in
2 min) , but when I try doing this in SQL using a cursor it takes hours.
The net effect is as follows.
ID, SubID
1001, 1
1001, 2
1001, 3
1002, 1
1003, 1
1003, 2
1003, 3
TIA
Rob
Rob Diamant wrote:
> I have a table that I am trying to bulk load 100K records via SQL.
> The table has a primary key of ID and SubID. I am creating a
> temporary table that will populate ID just fine, but is there a fast
> way I can run through these records and assign the SubID. I have no
> problem doing this in VB (it runs in 2 min) , but when I try doing
> this in SQL using a cursor it takes hours.
> The net effect is as follows.
> ID, SubID
> 1001, 1
> 1001, 2
> 1001, 3
> 1002, 1
> 1003, 1
> 1003, 2
> 1003, 3
> TIA
> Rob
Cursors are slow because they are not set-based. Do it in your
application and then bulk-load the data. I don't how we would know how
to help you assign a sub id since we don't know anything about your
requirements. Are you saying the SubID values do not exist in the file?
If so, how do you know how to assign them? How do you assign the IDs for
that matter?
David Gugick
Imceda Software
www.imceda.com
|||David,
Thanks for the info. I already can sequence the SubID's in my application. I
am trying to find a way to cheat (ie faster) by using SQL.
In a related topic is there a way to get the select the ROWID?
Rob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
> Rob Diamant wrote:
> Cursors are slow because they are not set-based. Do it in your application
> and then bulk-load the data. I don't how we would know how to help you
> assign a sub id since we don't know anything about your requirements. Are
> you saying the SubID values do not exist in the file? If so, how do you
> know how to assign them? How do you assign the IDs for that matter?
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Rob Diamant wrote:
> David,
> Thanks for the info. I already can sequence the SubID's in my
> application. I am trying to find a way to cheat (ie faster) by using
> SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
What is the ROWID as you mean it? Rows in SQL Server do not have ROW IDs
like they do in Oracle. If you just wanted an incremental ID, you could
use an IDENTITY column, and SQL Server would create the value for you.
But it appears from your sample data that your PK is the ID + SUBID. I
think adding the necesssary IDs in your application is the best option
based on the information you provided.
David Gugick
Imceda Software
www.imceda.com
|||I don't know if this will be faster than assigning the SubID values in your
app but the example below shows how you can accomplish the task using a
set-based technique.
CREATE TABLE MyTable
(
ID int NOT NULL,
SubID int NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
)
CREATE TABLE MyStagingTable
(
SequenceNumber int IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_MyStagingTable PRIMARY KEY,
ID int NOT NULL
)
INSERT INTO MyStagingTable
SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1002
UNION ALL SELECT 1003
UNION ALL SELECT 1003
UNION ALL SELECT 1003
CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
SequenceNumber)
INSERT INTO MyTable
SELECT ID,
(SELECT COUNT(*)
FROM MyStagingTable b
WHERE b.ID = a.ID AND
a.SequenceNumber <= b.SequenceNumber)
FROM MyStagingTable a
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob Diamant" <rob@.usi.com> wrote in message
news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
> David,
> Thanks for the info. I already can sequence the SubID's in my application.
> I am trying to find a way to cheat (ie faster) by using SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>
|||Dan,
Thanks, this should give me a great start.
Rob
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uJEdf91$EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I don't know if this will be faster than assigning the SubID values in your
>app but the example below shows how you can accomplish the task using a
>set-based technique.
> CREATE TABLE MyTable
> (
> ID int NOT NULL,
> SubID int NOT NULL,
> CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
> )
> CREATE TABLE MyStagingTable
> (
> SequenceNumber int IDENTITY(1, 1) NOT NULL
> CONSTRAINT PK_MyStagingTable PRIMARY KEY,
> ID int NOT NULL
> )
> INSERT INTO MyStagingTable
> SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1002
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
> SequenceNumber)
> INSERT INTO MyTable
> SELECT ID,
> (SELECT COUNT(*)
> FROM MyStagingTable b
> WHERE b.ID = a.ID AND
> a.SequenceNumber <= b.SequenceNumber)
> FROM MyStagingTable a
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob Diamant" <rob@.usi.com> wrote in message
> news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
>

Fast Sequencing

I have a table that I am trying to bulk load 100K records via SQL. The table
has a primary key of ID and SubID. I am creating a temporary table that will
populate ID just fine, but is there a fast way I can run through these
records and assign the SubID. I have no problem doing this in VB (it runs in
2 min) , but when I try doing this in SQL using a cursor it takes hours.
The net effect is as follows.
ID, SubID
1001, 1
1001, 2
1001, 3
1002, 1
1003, 1
1003, 2
1003, 3
TIA
RobRob Diamant wrote:
> I have a table that I am trying to bulk load 100K records via SQL.
> The table has a primary key of ID and SubID. I am creating a
> temporary table that will populate ID just fine, but is there a fast
> way I can run through these records and assign the SubID. I have no
> problem doing this in VB (it runs in 2 min) , but when I try doing
> this in SQL using a cursor it takes hours.
> The net effect is as follows.
> ID, SubID
> 1001, 1
> 1001, 2
> 1001, 3
> 1002, 1
> 1003, 1
> 1003, 2
> 1003, 3
> TIA
> Rob
Cursors are slow because they are not set-based. Do it in your
application and then bulk-load the data. I don't how we would know how
to help you assign a sub id since we don't know anything about your
requirements. Are you saying the SubID values do not exist in the file?
If so, how do you know how to assign them? How do you assign the IDs for
that matter?
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks for the info. I already can sequence the SubID's in my application. I
am trying to find a way to cheat (ie faster) by using SQL.
In a related topic is there a way to get the select the ROWID?
Rob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
> Rob Diamant wrote:
> Cursors are slow because they are not set-based. Do it in your application
> and then bulk-load the data. I don't how we would know how to help you
> assign a sub id since we don't know anything about your requirements. Are
> you saying the SubID values do not exist in the file? If so, how do you
> know how to assign them? How do you assign the IDs for that matter?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Rob Diamant wrote:
> David,
> Thanks for the info. I already can sequence the SubID's in my
> application. I am trying to find a way to cheat (ie faster) by using
> SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
What is the ROWID as you mean it? Rows in SQL Server do not have ROW IDs
like they do in Oracle. If you just wanted an incremental ID, you could
use an IDENTITY column, and SQL Server would create the value for you.
But it appears from your sample data that your PK is the ID + SUBID. I
think adding the necesssary IDs in your application is the best option
based on the information you provided.
David Gugick
Imceda Software
www.imceda.com|||I don't know if this will be faster than assigning the SubID values in your
app but the example below shows how you can accomplish the task using a
set-based technique.
CREATE TABLE MyTable
(
ID int NOT NULL,
SubID int NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
)
CREATE TABLE MyStagingTable
(
SequenceNumber int IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_MyStagingTable PRIMARY KEY,
ID int NOT NULL
)
INSERT INTO MyStagingTable
SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1002
UNION ALL SELECT 1003
UNION ALL SELECT 1003
UNION ALL SELECT 1003
CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
SequenceNumber)
INSERT INTO MyTable
SELECT ID,
(SELECT COUNT(*)
FROM MyStagingTable b
WHERE b.ID = a.ID AND
a.SequenceNumber <= b.SequenceNumber)
FROM MyStagingTable a
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob Diamant" <rob@.usi.com> wrote in message
news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
> David,
> Thanks for the info. I already can sequence the SubID's in my application.
> I am trying to find a way to cheat (ie faster) by using SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>|||Dan,
Thanks, this should give me a great start.
Rob
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uJEdf91$EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I don't know if this will be faster than assigning the SubID values in your
>app but the example below shows how you can accomplish the task using a
>set-based technique.
> CREATE TABLE MyTable
> (
> ID int NOT NULL,
> SubID int NOT NULL,
> CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
> )
> CREATE TABLE MyStagingTable
> (
> SequenceNumber int IDENTITY(1, 1) NOT NULL
> CONSTRAINT PK_MyStagingTable PRIMARY KEY,
> ID int NOT NULL
> )
> INSERT INTO MyStagingTable
> SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1002
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
> SequenceNumber)
> INSERT INTO MyTable
> SELECT ID,
> (SELECT COUNT(*)
> FROM MyStagingTable b
> WHERE b.ID = a.ID AND
> a.SequenceNumber <= b.SequenceNumber)
> FROM MyStagingTable a
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob Diamant" <rob@.usi.com> wrote in message
> news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
>

Fast Sequencing

I have a table that I am trying to bulk load 100K records via SQL. The table
has a primary key of ID and SubID. I am creating a temporary table that will
populate ID just fine, but is there a fast way I can run through these
records and assign the SubID. I have no problem doing this in VB (it runs in
2 min) , but when I try doing this in SQL using a cursor it takes hours.
The net effect is as follows.
ID, SubID
1001, 1
1001, 2
1001, 3
1002, 1
1003, 1
1003, 2
1003, 3
TIA
RobRob Diamant wrote:
> I have a table that I am trying to bulk load 100K records via SQL.
> The table has a primary key of ID and SubID. I am creating a
> temporary table that will populate ID just fine, but is there a fast
> way I can run through these records and assign the SubID. I have no
> problem doing this in VB (it runs in 2 min) , but when I try doing
> this in SQL using a cursor it takes hours.
> The net effect is as follows.
> ID, SubID
> 1001, 1
> 1001, 2
> 1001, 3
> 1002, 1
> 1003, 1
> 1003, 2
> 1003, 3
> TIA
> Rob
Cursors are slow because they are not set-based. Do it in your
application and then bulk-load the data. I don't how we would know how
to help you assign a sub id since we don't know anything about your
requirements. Are you saying the SubID values do not exist in the file?
If so, how do you know how to assign them? How do you assign the IDs for
that matter?
--
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks for the info. I already can sequence the SubID's in my application. I
am trying to find a way to cheat (ie faster) by using SQL.
In a related topic is there a way to get the select the ROWID?
Rob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
> Cursors are slow because they are not set-based. Do it in your application
> and then bulk-load the data. I don't how we would know how to help you
> assign a sub id since we don't know anything about your requirements. Are
> you saying the SubID values do not exist in the file? If so, how do you
> know how to assign them? How do you assign the IDs for that matter?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Rob Diamant wrote:
> David,
> Thanks for the info. I already can sequence the SubID's in my
> application. I am trying to find a way to cheat (ie faster) by using
> SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
What is the ROWID as you mean it? Rows in SQL Server do not have ROW IDs
like they do in Oracle. If you just wanted an incremental ID, you could
use an IDENTITY column, and SQL Server would create the value for you.
But it appears from your sample data that your PK is the ID + SUBID. I
think adding the necesssary IDs in your application is the best option
based on the information you provided.
--
David Gugick
Imceda Software
www.imceda.com|||I don't know if this will be faster than assigning the SubID values in your
app but the example below shows how you can accomplish the task using a
set-based technique.
CREATE TABLE MyTable
(
ID int NOT NULL,
SubID int NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
)
CREATE TABLE MyStagingTable
(
SequenceNumber int IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_MyStagingTable PRIMARY KEY,
ID int NOT NULL
)
INSERT INTO MyStagingTable
SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1002
UNION ALL SELECT 1003
UNION ALL SELECT 1003
UNION ALL SELECT 1003
CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
SequenceNumber)
INSERT INTO MyTable
SELECT ID,
(SELECT COUNT(*)
FROM MyStagingTable b
WHERE b.ID = a.ID AND
a.SequenceNumber <= b.SequenceNumber)
FROM MyStagingTable a
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob Diamant" <rob@.usi.com> wrote in message
news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
> David,
> Thanks for the info. I already can sequence the SubID's in my application.
> I am trying to find a way to cheat (ie faster) by using SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
>> Cursors are slow because they are not set-based. Do it in your
>> application and then bulk-load the data. I don't how we would know how to
>> help you assign a sub id since we don't know anything about your
>> requirements. Are you saying the SubID values do not exist in the file?
>> If so, how do you know how to assign them? How do you assign the IDs for
>> that matter?
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>|||Dan,
Thanks, this should give me a great start.
Rob
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uJEdf91$EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I don't know if this will be faster than assigning the SubID values in your
>app but the example below shows how you can accomplish the task using a
>set-based technique.
> CREATE TABLE MyTable
> (
> ID int NOT NULL,
> SubID int NOT NULL,
> CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
> )
> CREATE TABLE MyStagingTable
> (
> SequenceNumber int IDENTITY(1, 1) NOT NULL
> CONSTRAINT PK_MyStagingTable PRIMARY KEY,
> ID int NOT NULL
> )
> INSERT INTO MyStagingTable
> SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1002
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
> SequenceNumber)
> INSERT INTO MyTable
> SELECT ID,
> (SELECT COUNT(*)
> FROM MyStagingTable b
> WHERE b.ID = a.ID AND
> a.SequenceNumber <= b.SequenceNumber)
> FROM MyStagingTable a
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob Diamant" <rob@.usi.com> wrote in message
> news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
>> David,
>> Thanks for the info. I already can sequence the SubID's in my
>> application. I am trying to find a way to cheat (ie faster) by using SQL.
>> In a related topic is there a way to get the select the ROWID?
>> Rob
>>
>> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
>> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
>> Cursors are slow because they are not set-based. Do it in your
>> application and then bulk-load the data. I don't how we would know how
>> to help you assign a sub id since we don't know anything about your
>> requirements. Are you saying the SubID values do not exist in the file?
>> If so, how do you know how to assign them? How do you assign the IDs for
>> that matter?
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>>
>

Fast insert and select at the same time?

I have two tables: Account and AccountTransction. Each table contains more
than 20 million records. It one-to-many relationship between account and
AccountTransaction. The new records are constantly loading into each table
through text file by using DTS.
Question:
My team member insists that using cursor to insert record one by one to the
table to avoid affect (lock) the selection on these tables. . There are tons
of indexes on both tables for fast searching. The insertion process is
extremely slow. I recommended batch mode insertion, instead of one by one
using cursor. It is much more faster and efficient in terms of insertion,
but the selection while insertion going on is a little bit slower. What is
your suggestion? How can I achieve the fast insertion and fast selection at
the same time'
Is cursor alway a bad idea in terms of speed and performance?
Thanks a lot,
FlxI would never use a cursor to insert one row of data one by one...
Your colleague is smart to have worries about contention. However... it's
quite easy to do this in a safe manner. My standard technique for manaing a
situation like this is to:
* insert N number of rows per batch through an insert into stmt
* N is tested to ensure
- the insert happens fast enough to have a negligible impact on blocks
for selects
- durtion between batch inserts is long enough to ensure we're not
having a constant impact and quueses aren't growing
- but N is large enough to ensure I can insert enough records fast
enough such that the insert process isn't horrible slow.
I've been able to achieve VERY high insert and select throughput using
techniques like that...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"FLX" <nospam@.hotmail.com> wrote in message
news:e5rEoTiAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> I have two tables: Account and AccountTransction. Each table contains more
> than 20 million records. It one-to-many relationship between account and
> AccountTransaction. The new records are constantly loading into each table
> through text file by using DTS.
> Question:
> My team member insists that using cursor to insert record one by one to
the
> table to avoid affect (lock) the selection on these tables. . There are
tons
> of indexes on both tables for fast searching. The insertion process is
> extremely slow. I recommended batch mode insertion, instead of one by one
> using cursor. It is much more faster and efficient in terms of insertion,
> but the selection while insertion going on is a little bit slower. What is
> your suggestion? How can I achieve the fast insertion and fast selection
at
> the same time'
> Is cursor alway a bad idea in terms of speed and performance?
> Thanks a lot,
> Flx
>
>
>
>
>
>
>
>
>
>
>|||Here's an out-of-the-box idea.
Create new tables, same schema, so you have pairs to tables.
These new tables are for 'todays' data.
Create views to cover the pairs of tables. These are what your application/users look at / use.
The DTS populates the 'today' tables.
Once a day, at some light / quite period, stop the DTS. Copy / Move 'todays' data into the oringal, large table
Don't index the 'todays' tables, as they will (hopefully) be small enough to not need them. Or add only essential indexes.
Or only do this for the AccountTransaction table, and use the current method for the Account table.
If possible, you might want to drop the main table indexes just before you load the data from the 'todays' tables.
It depends, of course, on how quite your quite period will be.

Fast insert and select at the same time?

I have two tables: Account and AccountTransction. Each table contains more
than 20 million records. It one-to-many relationship between account and
AccountTransaction. The new records are constantly loading into each table
through text file by using DTS.
Question:
My team member insists that using cursor to insert record one by one to the
table to avoid affect (lock) the selection on these tables. . There are tons
of indexes on both tables for fast searching. The insertion process is
extremely slow. I recommended batch mode insertion, instead of one by one
using cursor. It is much more faster and efficient in terms of insertion,
but the selection while insertion going on is a little bit slower. What is
your suggestion? How can I achieve the fast insertion and fast selection at
the same time'
Is cursor alway a bad idea in terms of speed and performance?
Thanks a lot,
FlxI would never use a cursor to insert one row of data one by one...
Your colleague is smart to have worries about contention. However... it's
quite easy to do this in a safe manner. My standard technique for manaing a
situation like this is to:
* insert N number of rows per batch through an insert into stmt
* N is tested to ensure
- the insert happens fast enough to have a negligible impact on blocks
for selects
- durtion between batch inserts is long enough to ensure we're not
having a constant impact and quueses aren't growing
- but N is large enough to ensure I can insert enough records fast
enough such that the insert process isn't horrible slow.
I've been able to achieve VERY high insert and select throughput using
techniques like that...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"FLX" <nospam@.hotmail.com> wrote in message
news:e5rEoTiAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> I have two tables: Account and AccountTransction. Each table contains more
> than 20 million records. It one-to-many relationship between account and
> AccountTransaction. The new records are constantly loading into each table
> through text file by using DTS.
> Question:
> My team member insists that using cursor to insert record one by one to
the
> table to avoid affect (lock) the selection on these tables. . There are
tons
> of indexes on both tables for fast searching. The insertion process is
> extremely slow. I recommended batch mode insertion, instead of one by one
> using cursor. It is much more faster and efficient in terms of insertion,
> but the selection while insertion going on is a little bit slower. What is
> your suggestion? How can I achieve the fast insertion and fast selection
at
> the same time'
> Is cursor alway a bad idea in terms of speed and performance?
> Thanks a lot,
> Flx
>
>
>
>
>
>
>
>
>
>
>|||Here's an out-of-the-box idea.
Create new tables, same schema, so you have pairs to tables.
These new tables are for 'todays' data.
Create views to cover the pairs of tables. These are what your application/u
sers look at / use.
The DTS populates the 'today' tables.
Once a day, at some light / quite period, stop the DTS. Copy / Move 'todays'
data into the oringal, large table
Don't index the 'todays' tables, as they will (hopefully) be small enough to
not need them. Or add only essential indexes.
Or only do this for the AccountTransaction table, and use the current method
for the Account table.
If possible, you might want to drop the main table indexes just before you l
oad the data from the 'todays' tables.
It depends, of course, on how quite your quite period will be.