Inserting a blank line betwen groupings in my matrix report

I have a matrix report with 3 column groups. My main group is called
"Location" and then I dump out a bunch of data related to that location.
I want to insert a blank line before each new "Location" in my report but I
can't figure out how to do this with my matrix report.
Any ideas?Try putting this into expression for the location:
=(Fields!Location.Value+Environment.newline())
Good luck!
Peace,
Dan
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:6B279444-7502-43B3-B238-9C282A3C8858@.microsoft.com...
>I have a matrix report with 3 column groups. My main group is called
> "Location" and then I dump out a bunch of data related to that location.
> I want to insert a blank line before each new "Location" in my report but
> I
> can't figure out how to do this with my matrix report.
> Any ideas?

Insert Syntax Error

In SQL 7.0 SP3 , I am receiving this message...

Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near ')'.

When I try to execute this code from SQL Query Analyzer...

DECLARE @.DPPNumberCursor INT
DECLARE DPPNumberCursor Cursor for Select PPAP_ID
from ppap
where ppap_cancel <> "1" and
ppap_close <> "1" and
projectonhold <> "1"

OPEN DPPNumberCursor

Fetch Next From DPPNumberCursor

INTO @.dppnumbercursor

While @.@.Fetch_Status = 0
Begin
INSERT INTO APQPSubformTable (apqpsub_id)
Values (@.dppnumbercursor)

Bsically, I want to insert the number held in @.dppnumbercursor in the APQPSub_id field.

Any help would be appreciated.How about END at the end?|||...and DEALLOCATE/CLOSE would be very appropriate ;)|||OK, I'm an idiot...can you tell I'm a newbie? Thanks for not tearing me up too bad :-)

Thanks to both for your help....|||You're welcome...from both of us ;)|||Nobody is going to mention the double quotes?

Or the fact that s/he doesn't need a cursor at all?

INSERT INTO APQPSubformTable(apqpsub_id)
SELECT PPAP_ID
FROM ppap
WHERE ppap_cancel <> '1'
AND ppap_close <> '1'
AND projectonhold <> '1'|||I was thinking about it, but then decided not to...when he/she thanked "both" of us ;)|||It's been one of those days:-)

Just for my own edification, what about the double quotes?

The reason I used the cursor was because I needed to add two records into the APQPSubformtable for each open project in the ppap table. Here is the completed working code...

DECLARE @.DPPNumberCursor INT
DECLARE DPPNumberCursor Cursor for Select PPAP_ID
from ppap
where ppap_cancel <> "1" and
ppap_close <> "1" and
projectonhold <> "1"

OPEN DPPNumberCursor

Fetch Next From DPPNumberCursor

INTO @.dppnumbercursor

While @.@.Fetch_Status = 0
Begin
INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
Values (@.dppnumbercursor,"31","Start Of Production (SOP) [F4]","OP","0","G")

INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
Values (@.dppnumbercursor,"32","Stable Production (Review) [F5]","OP","0","G")

FETCH NEXT from DPPNumberCursor

INTO @.DPPNumberCursor

End

Close DPPNumberCursor|||Double-quotes are harmless as long as you know what your connection settings are when you compile your procedure/function/trigger/view (should be SET QUOTED_IDENTIFIER OFF).|||Just for my own edification, what about the double quotes?

It is a better practice to use single quotes to delimit strings. Double quotes can serve two purposes: delimit a string and delimit an identifyer (ie table name or column name). There is a QUOTED_IDENTIFYER property the determines which. Set to ON, everything within double quotes is supposed to be considered an Identifyer.

I've read in BOL that SLQ Server is 'not very rigorous' in enforcing this rule, depending on the length of the string in question. Small strings such as the ones you are using seem to work OK, but you'd be better off delimiting the strings correctly than relying on SQL Analyzer to interpret what it is you really mean.|||Don't Play with Settings...

INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
SELECT @.dppnumbercursor,'31','Start Of Production (SOP) [F4]','OP','0','G'
UNION ALL
SELECT @.dppnumbercursor,'32','Stable Production (Review) [F5]','OP','0','G'

Insert Query Problem

hi all i m trying to insert in my datbase this query but it is giving error at the line myCommand.ExecuteNonQuery();

myConnection.Open();
String insertCmd = "insert into CRtable(@.CR_NO, @.Asset_Type, @.PortIDNew, @.SpecialRequest, @.MovePortFrom, @.MovePortTo, @.MoveHardware, @.MoveSoftware, @.AllSelected, @.SpecialSoftwarePort, @.SpecialSoftwares, @.OtherSoftwares, @.FolderName, @.FolderPurpose, @.FolderUserIds, @.FolderRetentionPeriod, @.SpecialAccessOnExistFolder, @.AcessType, @.ExistFolderUserId, @.SpecialAcessOnIntranet, @.InternetUserId, @.MailId, @.HeadSet, @.Requester, @.RequestDateTime, @.Dept, @.PurposeOfCrForm, @.RequestedPort, @.ecode, @.qty, @.process, @.cost_evaluation, @.capital_exp, @.tat)";


SqlCommand myCommand = new SqlCommand(insertCmd,myConnection);myCommand.Parameters.Add("@.CR_NO",crm1);
myCommand.Parameters.Add("@.Asset_Type",StrAssetType2+StrAssetType);
myCommand.Parameters.Add("@.PortIdNew",StrPorts);
myCommand.Parameters.Add("@.SpecialRequest",StrSpecialRequest);
myCommand.Parameters.Add("@.MovePortFrom",txtAssetMovementFrom);
myCommand.Parameters.Add("@.MovePortTo",txtAssetMovementTo);
myCommand.Parameters.Add("@.MoveHardware",StrAssetHardware);
myCommand.Parameters.Add("@.MoveSoftware",StrAssetSoftware);
myCommand.Parameters.Add("@.AllSelected", StrAssetStatus);
myCommand.Parameters.Add("@.SpecialSoftwarePort",StrSpecialSoftwarePort);
myCommand.Parameters.Add("@.SpecialSoftwares",StrSpecialSoftware);
myCommand.Parameters.Add("@.OtherSoftwares",StrOtherSoftware);
myCommand.Parameters.Add("@.FolderName",StrFolderName);
myCommand.Parameters.Add("@.FolderPurpose","");//
myCommand.Parameters.Add("@.FolderUserIds",StrFolderUserId);
myCommand.Parameters.Add("@.FolderRetentionPeriod",StrFolderRetentionDays);
myCommand.Parameters.Add("@.SpecialAccessOnExistFolder","");//
myCommand.Parameters.Add("@.AcessType",StrAcessType);
myCommand.Parameters.Add("@.ExistFolderUserId",StrExistFolderUserId);
myCommand.Parameters.Add("@.SpecialAcessOnIntranet","");//
myCommand.Parameters.Add("@.InternetUserId","");//
myCommand.Parameters.Add("@.MailId","");//
myCommand.Parameters.Add("@.HeadSet","");//
myCommand.Parameters.Add("@.Requester",StrRequest);
myCommand.Parameters.Add("@.RequestDateTime",time);
myCommand.Parameters.Add("@.Dept",StrDept);
myCommand.Parameters.Add("@.PurposeOfCrForm",StrPurpose);
myCommand.Parameters.Add("@.RequestedPort"," ");//
myCommand.Parameters.Add("@.ecode",ecode);
myCommand.Parameters.Add("@.qty",quantity);
myCommand.Parameters.Add("@.process",proces);
myCommand.Parameters.Add("@.cost_evaluation",cost);
myCommand.Parameters.Add("@.capital_exp"," ");//
myCommand.Parameters.Add("@.tat",tat);
try
{
myCommand.ExecuteNonQuery();
}
catch (SqlException er)
{
Response.Write(er.StackTrace);
}

myConnection.Close();
}

the error mwssage is following

Line 1: Incorrect syntax near ')'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ')'.

Source Error:

Line 182:// try
Line 183:// {
Line 184: myCommand.ExecuteNonQuery();
Line 185:// }
Line 186:// catch (SqlException er)

Source File: c:\inetpub\wwwroot\cr request form\crpreview1.aspx.cs Line: 184

why this is occuring?

The correct structure for an INSERT statement is like this:

INSERT INTO someTable(column1, columns2)VALUES (@.param1, @.param2)

|||hi thanks for ur response but there is another problem actully I am passing some values in a parameter as blank resulting in incorrect formation of my InsertString.
it is be getting build as
"insert ito table values('val1',val2',)
Resulting in Error.But I have to pass these null values depending upon the Requet.Querystring form another page.It can be any value or it can not have any value .what should i do for it?|||hi the command run properly but the values r not inserting in the table .
what will be there.my query is
string insertCmd="insert into CRtable('" + crm1 + "','" + StrAssetType2 + StrAssetType +"','" + StrPorts + "','" + StrSpecialRequest + "','" + txtAssetMovementFrom + "','" + txtAssetMovementTo + "','" + StrAssetHardware + "','" + StrAssetSoftware + "','" + StrAssetStatus + "','" + StrSpecialSoftwarePort + "','" + StrSpecialSoftware + "','" + StrOtherSoftware + "','" + StrFolderName + "','" + null + "','" + StrFolderUserId + "','" + StrFolderRetentionDays + "','" + null + "','" + StrAcessType + "','" + StrExistFolderUserId + "','" + null + "','" + null + "','" + null + "','" + null + "','" + StrRequest + "','" + time + "','" + StrDept + "','" + StrPurpose + "','" + null + "','" + ecode + "','" + quantity + "','" + proces + "','" + cost + "','" + null + "'," + tat + ")";

but no insertion in table.|||

I agree with tmorton , first please make sure you use correct T-SQL syntax in your query. For example if you want to insert a row with some null fields, you should use such query:

insert into mytable(column1,column2,column3) values('val1',null,'val3')

|||

for those string variable, use ' ' for the value

www.cyberxforum.com

|||

ruchipunetha:

hi the command run properly but the values r not inserting in the table .
what will be there.my query is
string insertCmd="insert into CRtable('" + crm1 + "','" + StrAssetType2 + StrAssetType +"','" + StrPorts + "','" + StrSpecialRequest + "','" + txtAssetMovementFrom + "','" + txtAssetMovementTo + "','" + StrAssetHardware + "','" + StrAssetSoftware + "','" + StrAssetStatus + "','" + StrSpecialSoftwarePort + "','" + StrSpecialSoftware + "','" + StrOtherSoftware + "','" + StrFolderName + "','" + null + "','" + StrFolderUserId + "','" + StrFolderRetentionDays + "','" + null + "','" + StrAcessType + "','" + StrExistFolderUserId + "','" + null + "','" + null + "','" + null + "','" + null + "','" + StrRequest + "','" + time + "','" + StrDept + "','" + StrPurpose + "','" + null + "','" + ecode + "','" + quantity + "','" + proces + "','" + cost + "','" + null + "'," + tat + ")";

but no insertion in table.

Again, the correct syntax is:

INSERT INTO someTable(column1, columns2)VALUES (@.param1, @.param2)

Note that I used parameters, and note that there are 2 parts: column names and values to assign to those columns. Do not build a SQL statement like that, from data supplied by a UI. It is insecure.|||

i reckon that used parameters can prevent SQL injection hack..

www.cyberxforum.com

|||

yuan:

i reckon that used parameters can prevent SQL injection hack..

Yes, that's correct.|||i agree with u but the crm1 are string variable not constant i cannot put them in single quoto.

Insert query problem

I am getting this message
"Msg 128, Level 15, State 1, Line 2
The name 'A0000000000 B00C3124901 C00200603071' is not permitted in this
context. Only constants, expressions, or variables allowed here. Column name
s
are not permitted."
when i execute below query in store procedure which dynamic sql query and i
guess because @.strA and strB are string data might cuase problem as in vb.ne
t
i put single quote around it like values(' " & strA & " ',' "& strB " ')"
vb.net code... but i dont know that thing in sql server 2005 as it use singl
e
quote any body has any idea thanks
declare strA nvarchar(20), strB nvarchar(60)
@.sql='INSERT INTO [Dev Work].[dbo].[tbltemp](A,B) VALUES(' + @.strA + ','
+ @.strB + ')'how do you want the query string to look like?
is
'A0000000000 B00C3124901 C00200603071'
the value you are inserting
in that case
do this
'''A0000000000 B00C3124901 C00200603071'''
its 3 single quotes.
it would be better if you can try this out in query analyzer and find the
problem.
and why are you using dynamic SQL.
Can you paste the actual insert script that you are trying to build?
"amjad" wrote:

> I am getting this message
> "Msg 128, Level 15, State 1, Line 2
> The name 'A0000000000 B00C3124901 C00200603071' is not permitted in this
> context. Only constants, expressions, or variables allowed here. Column na
mes
> are not permitted."
> when i execute below query in store procedure which dynamic sql query and
i
> guess because @.strA and strB are string data might cuase problem as in vb.
net
> i put single quote around it like values(' " & strA & " ',' "& strB " ')"
> vb.net code... but i dont know that thing in sql server 2005 as it use sin
gle
> quote any body has any idea thanks
> declare strA nvarchar(20), strB nvarchar(60)
> @.sql='INSERT INTO [Dev Work].[dbo].[tbltemp](A,B) VALUES(' + @.strA + ','
> + @.strB + ')'|||amjad (amjad@.discussions.microsoft.com) writes:
> I am getting this message
> "Msg 128, Level 15, State 1, Line 2
> The name 'A0000000000 B00C3124901 C00200603071' is not permitted in this
> context. Only constants, expressions, or variables allowed here. Column
> names are not permitted."
> when i execute below query in store procedure which dynamic sql query
> and i guess because @.strA and strB are string data might cuase problem
> as in vb.net i put single quote around it like values(' " & strA & " ','
> "& strB " ')" vb.net code... but i dont know that thing in sql server
> 2005 as it use single quote any body has any idea thanks
> declare strA nvarchar(20), strB nvarchar(60)
> @.sql='INSERT INTO [Dev Work].[dbo].[tbltemp](A,B)
> VALUES(' + @.strA + ',' + @.strB + ')'
There is no quoting delimiters in the generated SQL command.
But you should not generate SQL in this way. First of all, it's a little
difficult to see why you would need to use dynamic SQL at all in a stored
procedure for a simple INSERT command. What's wrong with:
INSERT INTO [Dev Work].[dbo].[tbltemp](A,B) VALUES(@.strA, @.strB)
If there is more to it that you don't show, so dynamic SQL indeed is
necessary, you should use sp_executesql instead. This saves you from
the syntactic hassle you are running into now.
I have a longer article where I discuss dynamic SQL in general, and also
extensively cover sp_executesql. You find it on
http://www.sommarskog.se/dynamic_sql.html.
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|||thanks three quote solved the problem as i am doing some string manipulation
and validation on that data and put invalide data into temp table where i am
doing dynamic sql to insert one by one record. one thing more is their any
mod function in sql server like excel or vb thanks
"Omnibuzz" wrote:
> how do you want the query string to look like?
> is
> 'A0000000000 B00C3124901 C00200603071'
> the value you are inserting
> in that case
> do this
> '''A0000000000 B00C3124901 C00200603071'''
> its 3 single quotes.
> it would be better if you can try this out in query analyzer and find the
> problem.
> and why are you using dynamic SQL.
> Can you paste the actual insert script that you are trying to build?
> --
>
>
> "amjad" wrote:
>|||No sweat :)
--
"amjad" wrote:
> thanks three quote solved the problem as i am doing some string manipulati
on
> and validation on that data and put invalide data into temp table where i
am
> doing dynamic sql to insert one by one record. one thing more is their any
> mod function in sql server like excel or vb thanks
> "Omnibuzz" wrote:
>

Insert performance has seriously degraded after adding new Columns to Table

I have been searching the newsgroupps and books on line and can't find
anything that sounds like my issue. So, I'm coming here for help!
Here is what is happening:
Our app has a stored procedure that does some processing on the data.
Within the stored procedure there is are inserts into tables.
In our old version, this stored procedure completed in less than 5
minutes. 32000 rows were processed.
In our new version, the stored procedure took more than 14 hours to
complete.
The culprit was an insert into a table we added new columns to. That
was the ONLY change to the stored procedure.
Here are the table definitions:
OLD
CREATE TABLE reconcile_group (
OBJECT_ID varchar (13) NOT NULL ,
OPERATOR varchar (13) NOT NULL ,
RECONCILED_DATE datetime NOT NULL ,
CLIENT varchar (13) NOT NULL ,
REC_TYPE varchar (13) NOT NULL ,
LOCATION char (1) NOT NULL ,
ALGORITHM char (1) NOT NULL ,
VARIANCE char (1) NOT NULL ,
LATE_DEPOSIT int NOT NULL ,
IGN_TRAN_DATE char (1) NOT NULL ,
IGN_TRAN_CODE char (1) NOT NULL ,
MIS char (1) NOT NULL ,
MANAGER_STATS varchar (13) NOT NULL)
ON PRIMARY
NEW
CREATE TABLE reconcile_group (
OBJECT_ID varchar (13) NOT NULL ,
OPERATOR varchar (13) NOT NULL ,
RECONCILED_DATE datetime NOT NULL ,
CLIENT varchar (13) NOT NULL ,
REC_TYPE varchar (13) NOT NULL ,
LOCATION char (1) NOT NULL ,
ALGORITHM char (1) NOT NULL ,
VARIANCE char (1) NOT NULL ,
LATE_DEPOSIT int NOT NULL ,
IGN_TRAN_DATE char (1) NOT NULL ,
IGN_TRAN_CODE char (1) NOT NULL ,
MIS char (1) NOT NULL ,
MANAGER_STATS varchar (13) NOT NULL ,
IGN_UDF varchar (512) NULL ,
ADDITIONAL_INFO varchar (512) NULL
) ON PRIMARY
Here are the inserts:
OLD
INSERT INTO reconcile_group
(object_id,operator,reconciled_date,clie
nt,
rec_type,location,algorithm,variance,lat
e_deposit,
ign_tran_date,ign_tran_code,mis,manager_
stats)
VALUES
(@.v_last_oid, @.v_parm_operator, @.v_parm_rec_date,
@.v_item_acct, @.v_item_rec_type, 'A', 'N', 'F', 0,
'F', 'F', 'T', '0')
NEW
INSERT INTO reconcile_group
(object_id,operator,reconciled_date,clie
nt,
rec_type,location,algorithm,variance,lat
e_deposit,
ign_tran_date,ign_tran_code,mis,manager_
stats,ign_udf, additional_info)
VALUES
(@.v_last_oid, @.v_parm_operator, @.v_parm_rec_date,
@.v_item_acct, @.v_item_rec_type, 'A', 'N', 'F', 0,
'F', 'F', 'T', '0', 'F','')
That insert is the only code that changed in the whole procedure. When
we replace the new proc with the old one processing time goes back to
under 5 minutes.
There are no new indexes on the table. I have SET NOCOUNT ON in the
sp.
Am I missing something in the way of tuning? Does adding those 2
columns really affect the insert performance that much? What else can
i look at?
We have customers seeing the issue in production and we are able to
reproduce it in house.
We are running Sql 2000 sp3 on Windows 2003 server sp1.
Thanks for any advice.
LibbyIf the new columns are part of a clustered index (default for a primary
key), then this could impact the performance of inserts. Run DBCC SHOWCONTIG
to see what fragmentation may have resulted by altering the layout of this
table. Also, when the insert starts bogging down, check to see how much
transaction logging is going on.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"EElliott" <eelliott96@.gmail.com> wrote in message
news:1123090923.738117.74640@.z14g2000cwz.googlegroups.com...
>I have been searching the newsgroupps and books on line and can't find
> anything that sounds like my issue. So, I'm coming here for help!
> Here is what is happening:
> Our app has a stored procedure that does some processing on the data.
> Within the stored procedure there is are inserts into tables.
> In our old version, this stored procedure completed in less than 5
> minutes. 32000 rows were processed.
> In our new version, the stored procedure took more than 14 hours to
> complete.
> The culprit was an insert into a table we added new columns to. That
> was the ONLY change to the stored procedure.
> Here are the table definitions:
> OLD
> CREATE TABLE reconcile_group (
> OBJECT_ID varchar (13) NOT NULL ,
> OPERATOR varchar (13) NOT NULL ,
> RECONCILED_DATE datetime NOT NULL ,
> CLIENT varchar (13) NOT NULL ,
> REC_TYPE varchar (13) NOT NULL ,
> LOCATION char (1) NOT NULL ,
> ALGORITHM char (1) NOT NULL ,
> VARIANCE char (1) NOT NULL ,
> LATE_DEPOSIT int NOT NULL ,
> IGN_TRAN_DATE char (1) NOT NULL ,
> IGN_TRAN_CODE char (1) NOT NULL ,
> MIS char (1) NOT NULL ,
> MANAGER_STATS varchar (13) NOT NULL)
> ON PRIMARY
> NEW
> CREATE TABLE reconcile_group (
> OBJECT_ID varchar (13) NOT NULL ,
> OPERATOR varchar (13) NOT NULL ,
> RECONCILED_DATE datetime NOT NULL ,
> CLIENT varchar (13) NOT NULL ,
> REC_TYPE varchar (13) NOT NULL ,
> LOCATION char (1) NOT NULL ,
> ALGORITHM char (1) NOT NULL ,
> VARIANCE char (1) NOT NULL ,
> LATE_DEPOSIT int NOT NULL ,
> IGN_TRAN_DATE char (1) NOT NULL ,
> IGN_TRAN_CODE char (1) NOT NULL ,
> MIS char (1) NOT NULL ,
> MANAGER_STATS varchar (13) NOT NULL ,
> IGN_UDF varchar (512) NULL ,
> ADDITIONAL_INFO varchar (512) NULL
> ) ON PRIMARY
> Here are the inserts:
> OLD
> INSERT INTO reconcile_group
> (object_id,operator,reconciled_date,clie
nt,
> rec_type,location,algorithm,variance,lat
e_deposit,
> ign_tran_date,ign_tran_code,mis,manager_
stats)
> VALUES
> (@.v_last_oid, @.v_parm_operator, @.v_parm_rec_date,
> @.v_item_acct, @.v_item_rec_type, 'A', 'N', 'F', 0,
> 'F', 'F', 'T', '0')
> NEW
> INSERT INTO reconcile_group
> (object_id,operator,reconciled_date,clie
nt,
> rec_type,location,algorithm,variance,lat
e_deposit,
> ign_tran_date,ign_tran_code,mis,manager_
stats,ign_udf, additional_info)
> VALUES
> (@.v_last_oid, @.v_parm_operator, @.v_parm_rec_date,
> @.v_item_acct, @.v_item_rec_type, 'A', 'N', 'F', 0,
> 'F', 'F', 'T', '0', 'F','')
> That insert is the only code that changed in the whole procedure. When
> we replace the new proc with the old one processing time goes back to
> under 5 minutes.
> There are no new indexes on the table. I have SET NOCOUNT ON in the
> sp.
> Am I missing something in the way of tuning? Does adding those 2
> columns really affect the insert performance that much? What else can
> i look at?
> We have customers seeing the issue in production and we are able to
> reproduce it in house.
> We are running Sql 2000 sp3 on Windows 2003 server sp1.
> Thanks for any advice.
> Libby
>|||On 3 Aug 2005 10:42:03 -0700, EElliott wrote:

>I have been searching the newsgroupps and books on line and can't find
>anything that sounds like my issue. So, I'm coming here for help!
>Here is what is happening:
>Our app has a stored procedure that does some processing on the data.
>Within the stored procedure there is are inserts into tables.
>In our old version, this stored procedure completed in less than 5
>minutes. 32000 rows were processed.
>In our new version, the stored procedure took more than 14 hours to
>complete.
>The culprit was an insert into a table we added new columns to. That
>was the ONLY change to the stored procedure.
(snip)
Hi Libby,
The added columns require SQL Server to rebuild the pages when the rows
are updated (a bit mask with NULL markers for the new, nullable columns
has to be added, plus two length markers for the current length of the
varchar strings. And the strings themselves, of course). This can take a
while.
If I recall correctly, SQL Server won't do this for the complete table
when the ALTER TABLE statement is executed, but will postpone it until
the data is changed. I think it does the change on a page by page basis
(i.e. if one row in a page is changed, all rows are), but I don't know
that for sure. In any case, since some bytes have to be added to each
row, you'll probably run into page splits during the process as well.
Was this the first time that a major amout of rows in the table was
changed after the columns were added? If so, then this might be a
one-time problem. Next time you run the procedure, all rows have already
been converted.
Also, you mention 32000 rwos processed, but the statement you posted
will only add one single row. Are you using a cursor or other loop-based
code to process your data one row at a time? In 99% of all cases, such
code can be rewritten as setbased code, and the latter code is almost
always shorter AND quicker. If the slowness was indeed caused by the row
conversion, then a set-based approach might have caused the rebuilding
process to affect the whole table at once. Probably a lot quicker.
Finally: are there any triggers that fire on insertions to the
reconcile_group table?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||THanks for the replies so far.
I ran the dbcc showconfig and it showed some fragmentation, but it
wasn't terrible. I rebuilt the table and indexes anyway. THe process
showed no improvement.
To answer Hugo's questions:
When our app does the alter table to add the new columns, we also
update all of the rows in the table to hold a default vaule for the new
columns. So, it has already been processed once.
We do use cursors for processing the data. There's quite a bit of
other "stuff" it does, so I just pasted the relevant insert, since that
is the only thing that changed related to the stored proc.
And finally, There are no triggers on the DB at all.
We have finally gotten from the customer that they are seeing slowness
in other areas of the application as well. So, maybe this is not
isolated to one function. >:-( Thanks for telling us now! Perhaps
their DB needs some over all tuning.
If anyone has any other ideas, I'd appreciate them!
Thanks,
Libby