I asked this in the C# forum and got some input from some developers, now
I'm looking for some input from DBA's.
thanks
I'm working on a project and the lead developer wants to put all of the
business logic on the database side. He wants the proc to do all of the
logic and create XML on the fly, parse out data, etc, and then return the
final result to the business layer. I pushed back and said that the business
layer should do all of the logic, just have SQL return the data needed and
then have the code do all of the XML creation, parsing of data, etc. I was
told I was wrong and thats not the best practice.
Now, this app can hit the db by a total of 100-500 users a day, and having
the db do all of the processing can have a performance hit. Then he
mentioned something about using temp table in the database side, do a
select, then insert into the temp table, then do a select on the temp table
and do the business logic. I pushed back on that as well..
Now, I'm not a DBA but a developer and I've seen DB's take performance hits
when doing logic such as this on the database side, so my question is, which
would be the best solution for this?Hi
"Jon" wrote:
> I asked this in the C# forum and got some input from some developers, now
> I'm looking for some input from DBA's.
> thanks
> I'm working on a project and the lead developer wants to put all of the
> business logic on the database side. He wants the proc to do all of the
> logic and create XML on the fly, parse out data, etc, and then return the
> final result to the business layer. I pushed back and said that the business
> layer should do all of the logic, just have SQL return the data needed and
> then have the code do all of the XML creation, parsing of data, etc. I was
> told I was wrong and thats not the best practice.
> Now, this app can hit the db by a total of 100-500 users a day, and having
> the db do all of the processing can have a performance hit. Then he
> mentioned something about using temp table in the database side, do a
> select, then insert into the temp table, then do a select on the temp table
> and do the business logic. I pushed back on that as well..
> Now, I'm not a DBA but a developer and I've seen DB's take performance hits
> when doing logic such as this on the database side, so my question is, which
> would be the best solution for this?
>
You may want to look at http://msdn2.microsoft.com/en-us/library/ms973279.aspx
With client-server applications you can put business logic into the
database, but implementing a n-tier architecture you are moving away from
this and the business logic resides in one of the tiers.
As far as XML is concerned SQL Server can create and store XML data, you
would need to determine if the interface would work be better as a recordset
or XML and how much load the different methods would create.
John
Showing posts with label dba. Show all posts
Showing posts with label dba. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
architecture question
I asked this in the C# forum and got some input from some developers, now
I'm looking for some input from DBA's.
thanks
I'm working on a project and the lead developer wants to put all of the
business logic on the database side. He wants the proc to do all of the
logic and create XML on the fly, parse out data, etc, and then return the
final result to the business layer. I pushed back and said that the business
layer should do all of the logic, just have SQL return the data needed and
then have the code do all of the XML creation, parsing of data, etc. I was
told I was wrong and thats not the best practice.
Now, this app can hit the db by a total of 100-500 users a day, and having
the db do all of the processing can have a performance hit. Then he
mentioned something about using temp table in the database side, do a
select, then insert into the temp table, then do a select on the temp table
and do the business logic. I pushed back on that as well..
Now, I'm not a DBA but a developer and I've seen DB's take performance hits
when doing logic such as this on the database side, so my question is, which
would be the best solution for this?Hi
"Jon" wrote:
> I asked this in the C# forum and got some input from some developers, now
> I'm looking for some input from DBA's.
> thanks
> I'm working on a project and the lead developer wants to put all of the
> business logic on the database side. He wants the proc to do all of the
> logic and create XML on the fly, parse out data, etc, and then return the
> final result to the business layer. I pushed back and said that the busine
ss
> layer should do all of the logic, just have SQL return the data needed and
> then have the code do all of the XML creation, parsing of data, etc. I was
> told I was wrong and thats not the best practice.
> Now, this app can hit the db by a total of 100-500 users a day, and having
> the db do all of the processing can have a performance hit. Then he
> mentioned something about using temp table in the database side, do a
> select, then insert into the temp table, then do a select on the temp tabl
e
> and do the business logic. I pushed back on that as well..
> Now, I'm not a DBA but a developer and I've seen DB's take performance hit
s
> when doing logic such as this on the database side, so my question is, whi
ch
> would be the best solution for this?
>
You may want to look at [url]http://msdn2.microsoft.com/en-us/library/ms973279.aspx[/ur
l]
With client-server applications you can put business logic into the
database, but implementing a n-tier architecture you are moving away from
this and the business logic resides in one of the tiers.
As far as XML is concerned SQL Server can create and store XML data, you
would need to determine if the interface would work be better as a recordset
or XML and how much load the different methods would create.
Johnsql
I'm looking for some input from DBA's.
thanks
I'm working on a project and the lead developer wants to put all of the
business logic on the database side. He wants the proc to do all of the
logic and create XML on the fly, parse out data, etc, and then return the
final result to the business layer. I pushed back and said that the business
layer should do all of the logic, just have SQL return the data needed and
then have the code do all of the XML creation, parsing of data, etc. I was
told I was wrong and thats not the best practice.
Now, this app can hit the db by a total of 100-500 users a day, and having
the db do all of the processing can have a performance hit. Then he
mentioned something about using temp table in the database side, do a
select, then insert into the temp table, then do a select on the temp table
and do the business logic. I pushed back on that as well..
Now, I'm not a DBA but a developer and I've seen DB's take performance hits
when doing logic such as this on the database side, so my question is, which
would be the best solution for this?Hi
"Jon" wrote:
> I asked this in the C# forum and got some input from some developers, now
> I'm looking for some input from DBA's.
> thanks
> I'm working on a project and the lead developer wants to put all of the
> business logic on the database side. He wants the proc to do all of the
> logic and create XML on the fly, parse out data, etc, and then return the
> final result to the business layer. I pushed back and said that the busine
ss
> layer should do all of the logic, just have SQL return the data needed and
> then have the code do all of the XML creation, parsing of data, etc. I was
> told I was wrong and thats not the best practice.
> Now, this app can hit the db by a total of 100-500 users a day, and having
> the db do all of the processing can have a performance hit. Then he
> mentioned something about using temp table in the database side, do a
> select, then insert into the temp table, then do a select on the temp tabl
e
> and do the business logic. I pushed back on that as well..
> Now, I'm not a DBA but a developer and I've seen DB's take performance hit
s
> when doing logic such as this on the database side, so my question is, whi
ch
> would be the best solution for this?
>
You may want to look at [url]http://msdn2.microsoft.com/en-us/library/ms973279.aspx[/ur
l]
With client-server applications you can put business logic into the
database, but implementing a n-tier architecture you are moving away from
this and the business logic resides in one of the tiers.
As far as XML is concerned SQL Server can create and store XML data, you
would need to determine if the interface would work be better as a recordset
or XML and how much load the different methods would create.
Johnsql
Thursday, February 16, 2012
Append information to data already in a column
Just recently I was thrown into the DBA roll... needless to say I'm not
a DBA. The customer has requested a query to take a column full of
numerical data (a 6 digit number) and run a query to append a .00
behind that number for every record in that column. Is this possible?
I've checked the online books and have done countless searches for a
hint of a query to no avail.
Any and all help would be greatly appreciated.
Thank you,
JIf you just want to represent the (integer?) number as decimal with '.00'
SELECT CAST([YourNumber] AS decimal(8,2)) AS "number"
FROM [YourTable]
If you actually want to change how the number is stored in the database:
ALTER TABLE [YourTable] ALTER COLUMN [YourNumber] decimal(8,2)
"JMiller" wrote:
> Just recently I was thrown into the DBA roll... needless to say I'm not
> a DBA. The customer has requested a query to take a column full of
> numerical data (a 6 digit number) and run a query to append a .00
> behind that number for every record in that column. Is this possible?
> I've checked the online books and have done countless searches for a
> hint of a query to no avail.
> Any and all help would be greatly appreciated.
> Thank you,
> J
>|||You have not told us the current data type of the column, and that
makes all the difference.
If it is an integer (bigint, int, smallint, tinyint) then the data
type would need to change to DECIMAL (or NUMERIC) with two decimal
places.
If it is already a decimal (or numeric) then the type has to change to
add two decimal places, being careful to keep sufficient length.
If the column is character, it needs to be lengthened by three
characters, and then updated to append the '.00' string after the data
that is already there.
If the column is varchar, it MIGHT need to be lengthened, and then it
would be updated as for char.
So, what is the column's data type?
Roy
On 15 Feb 2006 15:16:36 -0800, "JMiller" <Gloranard@.gmail.com> wrote:
>Just recently I was thrown into the DBA roll... needless to say I'm not
>a DBA. The customer has requested a query to take a column full of
>numerical data (a 6 digit number) and run a query to append a .00
>behind that number for every record in that column. Is this possible?
>I've checked the online books and have done countless searches for a
>hint of a query to no avail.
>Any and all help would be greatly appreciated.
>Thank you,
>J|||I apologize for forgetting to add that important piece of information.
The table is currently configured varchar (25).|||>I apologize for forgetting to add that important piece of information.
> The table is currently configured varchar (25).
update mytable set mycolumn = rtrim(mycolumn) + '.00'
where mycolumn is not null
Things to consider.
* The maximum size of any value in mycolumn is less than 23 (since you are
adding three additional characters). How to do this: select
max(len(mycolumn)) from mytable
* Verify that this is actually appropriate for the data found in the column
for all rows. In other words, are all values "properly" numeric.
* Perhaps the better approach is to change the datatype of the column.
Alternatively, create a computed column (or view) that does this for you.
Why? Well, the values that are currently in the table got there somehow.
It is likely that this functionality will continue to use the same format
when inserting or updating rows.
* You might need additional criteria in your where clause. You might not
want to change an empty string to '.00' (which is what the above query will
do).
* For testing purposes, you can wrap the above statement in a transaction.
If you rollback the transaction, you can issue the statement multiple times
and view the results. E.g.,
begin tran
update mytable set ...
select mycolumn from mytable
rollback tran|||Thanks you both for the help, it worked like a champ. Thank you, thank
you, thank you!
a DBA. The customer has requested a query to take a column full of
numerical data (a 6 digit number) and run a query to append a .00
behind that number for every record in that column. Is this possible?
I've checked the online books and have done countless searches for a
hint of a query to no avail.
Any and all help would be greatly appreciated.
Thank you,
JIf you just want to represent the (integer?) number as decimal with '.00'
SELECT CAST([YourNumber] AS decimal(8,2)) AS "number"
FROM [YourTable]
If you actually want to change how the number is stored in the database:
ALTER TABLE [YourTable] ALTER COLUMN [YourNumber] decimal(8,2)
"JMiller" wrote:
> Just recently I was thrown into the DBA roll... needless to say I'm not
> a DBA. The customer has requested a query to take a column full of
> numerical data (a 6 digit number) and run a query to append a .00
> behind that number for every record in that column. Is this possible?
> I've checked the online books and have done countless searches for a
> hint of a query to no avail.
> Any and all help would be greatly appreciated.
> Thank you,
> J
>|||You have not told us the current data type of the column, and that
makes all the difference.
If it is an integer (bigint, int, smallint, tinyint) then the data
type would need to change to DECIMAL (or NUMERIC) with two decimal
places.
If it is already a decimal (or numeric) then the type has to change to
add two decimal places, being careful to keep sufficient length.
If the column is character, it needs to be lengthened by three
characters, and then updated to append the '.00' string after the data
that is already there.
If the column is varchar, it MIGHT need to be lengthened, and then it
would be updated as for char.
So, what is the column's data type?
Roy
On 15 Feb 2006 15:16:36 -0800, "JMiller" <Gloranard@.gmail.com> wrote:
>Just recently I was thrown into the DBA roll... needless to say I'm not
>a DBA. The customer has requested a query to take a column full of
>numerical data (a 6 digit number) and run a query to append a .00
>behind that number for every record in that column. Is this possible?
>I've checked the online books and have done countless searches for a
>hint of a query to no avail.
>Any and all help would be greatly appreciated.
>Thank you,
>J|||I apologize for forgetting to add that important piece of information.
The table is currently configured varchar (25).|||>I apologize for forgetting to add that important piece of information.
> The table is currently configured varchar (25).
update mytable set mycolumn = rtrim(mycolumn) + '.00'
where mycolumn is not null
Things to consider.
* The maximum size of any value in mycolumn is less than 23 (since you are
adding three additional characters). How to do this: select
max(len(mycolumn)) from mytable
* Verify that this is actually appropriate for the data found in the column
for all rows. In other words, are all values "properly" numeric.
* Perhaps the better approach is to change the datatype of the column.
Alternatively, create a computed column (or view) that does this for you.
Why? Well, the values that are currently in the table got there somehow.
It is likely that this functionality will continue to use the same format
when inserting or updating rows.
* You might need additional criteria in your where clause. You might not
want to change an empty string to '.00' (which is what the above query will
do).
* For testing purposes, you can wrap the above statement in a transaction.
If you rollback the transaction, you can issue the statement multiple times
and view the results. E.g.,
begin tran
update mytable set ...
select mycolumn from mytable
rollback tran|||Thanks you both for the help, it worked like a champ. Thank you, thank
you, thank you!
Sunday, February 12, 2012
ap_ prefix to stored procedures
What does the ap_ stand for as a prefix to stored procedures?
A DBA tried to teach me something about the prefixes etc.. but I can't recall what it meant.
In this case ap_ may represent "ONE" persons naming convention?
something procedure...
other common ones in our database are dt_ and zz_
Its ok.. I know your all busy..
It is probably any naming convention I want..
I just thought it might be a case where the majority of SQL Developers use the same naming conventions.
Thanks.
||| It is probably just some naming convention that the previous developers or dba's had. I have seen <Action><Table>, sp_<action><table>, and many others in the past.
Subscribe to:
Posts (Atom)