Performance - php scripts

A vendor wrote a php script to manipulate date within SQL Server, basically
process data from 1 table and copy the data to multiple tables within the
same server. He said the preformance for others client run really fast,
except for ours. Our process run about 1 record (40 fields) per second. fo
r
table with 200K records, processing time is more than 8 hours, this is
unexceptable for our environment.
I am trying to figure out what I need to do within SQL server to speed up
the process.
Truncate log or Transactions?
Any help is greatly appreciate?
Thanks,
Culam8 Hours for 200K rows. That must be some really poor script. I don't know
what it is doing but it should only take a few minutes if done correctly.
Without seeinghte code it is pretty hard to say. One thing that when you are
doing a lot of data manipulation you should ensure you have plenty of room
in the log file and it should be placed on a different drive array than the
data file. Preferably a Raid 1.
Andrew J. Kelly SQL MVP
"culam" <culam@.discussions.microsoft.com> wrote in message
news:465DB2E9-14AA-4AE2-B83F-A79C73458BD3@.microsoft.com...
>A vendor wrote a php script to manipulate date within SQL Server, basically
> process data from 1 table and copy the data to multiple tables within the
> same server. He said the preformance for others client run really fast,
> except for ours. Our process run about 1 record (40 fields) per second.
> for
> table with 200K records, processing time is more than 8 hours, this is
> unexceptable for our environment.
> I am trying to figure out what I need to do within SQL server to speed up
> the process.
> Truncate log or Transactions?
> Any help is greatly appreciate?
> Thanks,
> Culam
>|||Unfortunately, we don't have access to the codes and the Client insists ther
e
codes is not the problem.
How can I verify if log file is the problem?
Culam
"Andrew J. Kelly" wrote:

> 8 Hours for 200K rows. That must be some really poor script. I don't kno
w
> what it is doing but it should only take a few minutes if done correctly.
> Without seeinghte code it is pretty hard to say. One thing that when you a
re
> doing a lot of data manipulation you should ensure you have plenty of room
> in the log file and it should be placed on a different drive array than th
e
> data file. Preferably a Raid 1.
> --
> Andrew J. Kelly SQL MVP
>
> "culam" <culam@.discussions.microsoft.com> wrote in message
> news:465DB2E9-14AA-4AE2-B83F-A79C73458BD3@.microsoft.com...
>
>|||I doubt that is the case but anyway here are some links that may help you
pinpoint what the bottlenecks are.
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"culam" <culam@.discussions.microsoft.com> wrote in message
news:ED0F7189-D1C3-4B41-98EE-22F91FCCDB14@.microsoft.com...
> Unfortunately, we don't have access to the codes and the Client insists
> there
> codes is not the problem.
> How can I verify if log file is the problem?
> Culam
> "Andrew J. Kelly" wrote:
>

Perform calculation using sql script

Is it possible to multiply a vaiable and a set column and update table with
new data? I need to estimate future net income using a predetermined
percentage against the previous years net income numbers. I use this to run
projection calculations against estimated annual net income. The formula
would look like this:
Projected Net Income = Previous Net Income + (Previous Net Income * Percent
Increase)
Table1: INCOME
Fields: NetIncome, DataYear, ProjectedNetIncome
Table2: PROJECTION
ProjectedYr, PercentIncrease
I would like to use PercentIncrease as a variable (this number can change
year to year and give me the ability to change percentage.)
Below is what i have been trying ... i know the code is incorrect and its at
the set statement (multipling variable against netincome field).
This is logically what I want to do but I know the code is all off...
any suggestions on how to correct?
/* Declare Variables */
DECLARE @.CurrentYr Numeric(9)
DECLARE @.IncomeIncrease Numeric(9)
SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.
If you could post DDL and a little sample data + your expected results, it
would help immensely. This assumes that the INCOME and PROJECTION Tables
are related via the DataYear and ProjectYear columns. If the INCOME table
has a row for DataYear = 2003, it will only update if the PROJECTION table
has a row for DataYear = 2003.
UPDATE INCOME SET ProjectedNetIncome = i.NetIncome + (i.NetIncome *
p.PercentIncrease)
FROM INCOME i, PROJECTION p
WHERE i.DataYear = p.ProjectYear
But it's hard to be certain without knowing where you're starting or where
you want to end up...
"Rob" <temp@.dstek.com> wrote in message
news:eOm9mEDTFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it possible to multiply a vaiable and a set column and update table
> with
> new data? I need to estimate future net income using a predetermined
> percentage against the previous years net income numbers. I use this to
> run
> projection calculations against estimated annual net income. The formula
> would look like this:
> Projected Net Income = Previous Net Income + (Previous Net Income *
> Percent
> Increase)
> Table1: INCOME
> Fields: NetIncome, DataYear, ProjectedNetIncome
> Table2: PROJECTION
> ProjectedYr, PercentIncrease
> I would like to use PercentIncrease as a variable (this number can change
> year to year and give me the ability to change percentage.)
> Below is what i have been trying ... i know the code is incorrect and its
> at
> the set statement (multipling variable against netincome field).
> This is logically what I want to do but I know the code is all off...
> any suggestions on how to correct?
> /* Declare Variables */
> DECLARE @.CurrentYr Numeric(9)
> DECLARE @.IncomeIncrease Numeric(9)
> SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
> SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>

Perform calculation using sql script

Is it possible to multiply a vaiable and a set column and update table with
new data? I need to estimate future net income using a predetermined
percentage against the previous years net income numbers. I use this to run
projection calculations against estimated annual net income. The formula
would look like this:
Projected Net Income = Previous Net Income + (Previous Net Income * Percent
Increase)
Table1: INCOME
Fields: NetIncome, DataYear, ProjectedNetIncome
Table2: PROJECTION
ProjectedYr, PercentIncrease
I would like to use PercentIncrease as a variable (this number can change
year to year and give me the ability to change percentage.)
Below is what i have been trying ... i know the code is incorrect and its at
the set statement (multipling variable against netincome field).
This is logically what I want to do but I know the code is all off...
any suggestions on how to correct?
/* Declare Variables */
DECLARE @.CurrentYr Numeric(9)
DECLARE @.IncomeIncrease Numeric(9)
SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrea
se)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.If you could post DDL and a little sample data + your expected results, it
would help immensely. This assumes that the INCOME and PROJECTION Tables
are related via the DataYear and ProjectYear columns. If the INCOME table
has a row for DataYear = 2003, it will only update if the PROJECTION table
has a row for DataYear = 2003.
UPDATE INCOME SET ProjectedNetIncome = i.NetIncome + (i.NetIncome *
p.PercentIncrease)
FROM INCOME i, PROJECTION p
WHERE i.DataYear = p.ProjectYear
But it's hard to be certain without knowing where you're starting or where
you want to end up...
"Rob" <temp@.dstek.com> wrote in message
news:eOm9mEDTFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it possible to multiply a vaiable and a set column and update table
> with
> new data? I need to estimate future net income using a predetermined
> percentage against the previous years net income numbers. I use this to
> run
> projection calculations against estimated annual net income. The formula
> would look like this:
> Projected Net Income = Previous Net Income + (Previous Net Income *
> Percent
> Increase)
> Table1: INCOME
> Fields: NetIncome, DataYear, ProjectedNetIncome
> Table2: PROJECTION
> ProjectedYr, PercentIncrease
> I would like to use PercentIncrease as a variable (this number can change
> year to year and give me the ability to change percentage.)
> Below is what i have been trying ... i know the code is incorrect and its
> at
> the set statement (multipling variable against netincome field).
> This is logically what I want to do but I know the code is all off...
> any suggestions on how to correct?
> /* Declare Variables */
> DECLARE @.CurrentYr Numeric(9)
> DECLARE @.IncomeIncrease Numeric(9)
> SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
> SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrea
se)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>

Perform calculation using sql script

Is it possible to multiply a vaiable and a set column and update table with
new data? I need to estimate future net income using a predetermined
percentage against the previous years net income numbers. I use this to run
projection calculations against estimated annual net income. The formula
would look like this:
Projected Net Income = Previous Net Income + (Previous Net Income * Percent
Increase)
Table1: INCOME
Fields: NetIncome, DataYear, ProjectedNetIncome
Table2: PROJECTION
ProjectedYr, PercentIncrease
I would like to use PercentIncrease as a variable (this number can change
year to year and give me the ability to change percentage.)
Below is what i have been trying ... i know the code is incorrect and its at
the set statement (multipling variable against netincome field).
This is logically what I want to do but I know the code is all off...
any suggestions on how to correct?
/* Declare Variables */
DECLARE @.CurrentYr Numeric(9)
DECLARE @.IncomeIncrease Numeric(9)
SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.If you could post DDL and a little sample data + your expected results, it
would help immensely. This assumes that the INCOME and PROJECTION Tables
are related via the DataYear and ProjectYear columns. If the INCOME table
has a row for DataYear = 2003, it will only update if the PROJECTION table
has a row for DataYear = 2003.
UPDATE INCOME SET ProjectedNetIncome = i.NetIncome + (i.NetIncome *
p.PercentIncrease)
FROM INCOME i, PROJECTION p
WHERE i.DataYear = p.ProjectYear
But it's hard to be certain without knowing where you're starting or where
you want to end up...
"Rob" <temp@.dstek.com> wrote in message
news:eOm9mEDTFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it possible to multiply a vaiable and a set column and update table
> with
> new data? I need to estimate future net income using a predetermined
> percentage against the previous years net income numbers. I use this to
> run
> projection calculations against estimated annual net income. The formula
> would look like this:
> Projected Net Income = Previous Net Income + (Previous Net Income *
> Percent
> Increase)
> Table1: INCOME
> Fields: NetIncome, DataYear, ProjectedNetIncome
> Table2: PROJECTION
> ProjectedYr, PercentIncrease
> I would like to use PercentIncrease as a variable (this number can change
> year to year and give me the ability to change percentage.)
> Below is what i have been trying ... i know the code is incorrect and its
> at
> the set statement (multipling variable against netincome field).
> This is logically what I want to do but I know the code is all off...
> any suggestions on how to correct?
> /* Declare Variables */
> DECLARE @.CurrentYr Numeric(9)
> DECLARE @.IncomeIncrease Numeric(9)
> SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
> SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>