Hey I need to know the overhead associated with SQL Server tables?
And what the overhead is comprised of ,would also be helpful.
Pleasemay i inquire why you are asking? yes, SQL Server tables have some overhead, and if i told you it was only 7%, would you be happy?|||Perhaps he means in comparison with Oracle.
Oracle incurs a significant overhead in the creation of tables, which is why the use of temporary tables in Oracle is discourage. SQL Server incurs very little overhead in creating tables, and temporary tables become a valuable tool for efficient coding.|||Trying to determine the size of the database tables, so I can figure out which queries would be the slowest then create a physical schema.
So It my table is 1MB of records its size is 1.07MB with Overhead,
What would the Overhead be comprised of? Indexes or?|||Temporary Tables, table in memory right, How would I create one do I use a Join?|||Nate,
No offense, but what is the extent of your database experience? We'll do our best to help you out here, but you seem unsure of even what questions to ask. The ones you have posted so far have little relevance to the database design process.
Start with a logical model. What is the purpose of the database you are designing?|||Ive made a number of databases Paradox, MS Access, Oracle, MySQL, Interbase, I probably ask stupid questions cause I don't know the answer and I havn't had years of experience, Im a final year bachelor of Computing systems, though Im previously trained in Graphic Design,
Im not an expert but It seems logical to me to understand how much data is being dealt with in a database when producing a physical schema? and even though seven percent it seems like relevant information, but your welcome to convince me otherwise?
And if you mean database design process you would be meaning conceptual logical physical right? which would be the same process Im following except I ask questions because Ive looked for the answers and havn't found them.
Plus the question was about how SQL server deals with tables more so than the design of my physical schema.|||but where did the "overhead" question come from?
let me ask you this, if you were in the market to buy a computer, why would one of the first question you ask be how much heat the motherboard puts out? surely the fan can take care of it, yes?
it's the same with table "overhead" -- what does it matter?
by the way, i pulled the number 7% out of the air|||A motherboard doesn't take up HDD space?
If I have a table/s 1GB or expected at 1GB and there is an overhead of 5% then I actually have used 1.05 GB and if the DB grows even larger then I would need more extra space? And if I omit any associated overhead from Analysis, I would then be likely miss calculate how long it will take to need to increase DB size (Hosted Server) based on the growth of site records. (But is overhead consistent with the size of the table? or Fixed?).
The question came from when designing physical schema for Oracle where the calculations had to include overhead.|||Nate1 - you don't need to consider this sort of "overhead" for SQL Server. If you really care then the overhead is predominantly page header information, some in row information (for example the null state of columns, position & length of variable length columns, position of fixed length columns), array corrolating logical order with physical page order etc.. There are also a few bytes in other system pages. As far as indexes go - it all depends how you index.
I can assure you that none of the contributers here ever consider this when designing a SQL Server database (at either the physical or logical level).|||I wouldn't go so far as to say he should not consider overhead, but it is certainly way down the list of priorities. The first step must be a logical model, then the physical model, and then the hardware requirements (where overhead may come into play).|||The overhead per row will be fixed. However, there are going to be a number of things that can not be calculated. For example: Will your clustered index likely produce page splits? How many? Will there be updates to varchar fields that will induce page splits? What will their distribution be? What will the maximum amount of fragmentation be before you reindex? How about the non-clustered indexes, will the insert or update pattern induce page splits there? Are you planning on implementing table/index partitioning? Will there be user defined statistics associated with the table? How about text/image data? What is the maximum number of rows per page you will be able to fit in? Will data be deleted/purged (thus leaving empty gaps in the table)?
Short answer, multiply total number of bytes in the row by the number of rows, then multiply by three....unless you want to answer all of the above questions.|||I wonder how much overhead my engine in my care takes up...oh wait, if I don't have an engine....I guess that would cause other problems|||Exactly the point I've been trying to make. Whatever the overhead will be, you have to deal with it. The only other option is not to create your application.|||Exactly the point I've been trying to make. Whatever the overhead will be, you have to deal with it. The only other option is not to create your application.
So...why didn't you just say so?|||Exactly the point I've been trying to make. Whatever the overhead will be, you have to deal with it. exactly the point i was trying to make (motherboard heat? fan?) -- the dbms will take care of the overhead, you yourself won't have to deal with it|||Exactly the point I was trying to ma... oh wait a minute - no I was making another point.|||exactly the point i was trying to make (motherboard heat? fan?) -- the dbms will take care of the overhead, you yourself won't have to deal with it
I still like mine better
Maybe he could just use 1 table
Showing posts with label overhead. Show all posts
Showing posts with label overhead. Show all posts
Monday, February 20, 2012
Overhead of Multiple SQL Calls
Hey folks,
I've got a client server C# Windows Form app that communicates with a SQL
Server database backend. The number of users is less than 50.
I'm wondering what the difference in overhead is when say:
a) A Stored Procedure is called ten times, returning 1 record each time
VS.
b) The same Stored Procedure is called once returning 10 records
Due to connection pooling I would think the overhead is somewhat negligible,
especially when there's less than 50 users. I know option 'b' would be the
preffered method when it comes to database communication, but is there
really a BIG difference between the two when dealing with this few users?
Thanks!
(also posted on windows.forms newsgroup on Feb 25 with no response)Hi John,
I would think the network traffic would be significantly greater when
submiting 10 calls to get 1 record each as opposed to 1 call to get 10
records. That's automatically 10X the client-to-server network traffic.
Constantly opening and closing connections, as well as maintaining
connections that aren't in use, can use up a lot of resources on SQL Server
also. Now whether this should cause problems with a small number of users
is another story... It probably depends on how they're using the server...
Are they running automated reports that are requesting records one at a time
and might include thousands of separate requests? Or maybe they're just
accessing 30 records/hour on average each?
Connection pooling helps, but it can't compensate completely for a design
flaw.
Are you using C#.NET? If so, you might look into ADO.NET. ADO.NET is
designed to handle a lot of the management issues for you.
Thanks,
Mike C.
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>|||You have that many today but what about tomorrow? Seriously you should
always keep scalability in mind when designing the app and the schema. In
your example you are incurring about 10 times the overhead on not only the
server but the network and the client. There certainly may be times when it
might make sense to do it the A way but if you can do it the B way then you
should.
--
Andrew J. Kelly SQL MVP
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>
I've got a client server C# Windows Form app that communicates with a SQL
Server database backend. The number of users is less than 50.
I'm wondering what the difference in overhead is when say:
a) A Stored Procedure is called ten times, returning 1 record each time
VS.
b) The same Stored Procedure is called once returning 10 records
Due to connection pooling I would think the overhead is somewhat negligible,
especially when there's less than 50 users. I know option 'b' would be the
preffered method when it comes to database communication, but is there
really a BIG difference between the two when dealing with this few users?
Thanks!
(also posted on windows.forms newsgroup on Feb 25 with no response)Hi John,
I would think the network traffic would be significantly greater when
submiting 10 calls to get 1 record each as opposed to 1 call to get 10
records. That's automatically 10X the client-to-server network traffic.
Constantly opening and closing connections, as well as maintaining
connections that aren't in use, can use up a lot of resources on SQL Server
also. Now whether this should cause problems with a small number of users
is another story... It probably depends on how they're using the server...
Are they running automated reports that are requesting records one at a time
and might include thousands of separate requests? Or maybe they're just
accessing 30 records/hour on average each?
Connection pooling helps, but it can't compensate completely for a design
flaw.
Are you using C#.NET? If so, you might look into ADO.NET. ADO.NET is
designed to handle a lot of the management issues for you.
Thanks,
Mike C.
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>|||You have that many today but what about tomorrow? Seriously you should
always keep scalability in mind when designing the app and the schema. In
your example you are incurring about 10 times the overhead on not only the
server but the network and the client. There certainly may be times when it
might make sense to do it the A way but if you can do it the B way then you
should.
--
Andrew J. Kelly SQL MVP
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>
Overhead of Multiple SQL Calls
Hey folks,
I've got a client server C# Windows Form app that communicates with a SQL
Server database backend. The number of users is less than 50.
I'm wondering what the difference in overhead is when say:
a) A Stored Procedure is called ten times, returning 1 record each time
VS.
b) The same Stored Procedure is called once returning 10 records
Due to connection pooling I would think the overhead is somewhat negligible,
especially when there's less than 50 users. I know option 'b' would be the
preffered method when it comes to database communication, but is there
really a BIG difference between the two when dealing with this few users?
Thanks!
(also posted on windows.forms newsgroup on Feb 25 with no response)Hi John,
I would think the network traffic would be significantly greater when
submiting 10 calls to get 1 record each as opposed to 1 call to get 10
records. That's automatically 10X the client-to-server network traffic.
Constantly opening and closing connections, as well as maintaining
connections that aren't in use, can use up a lot of resources on SQL Server
also. Now whether this should cause problems with a small number of users
is another story... It probably depends on how they're using the server...
Are they running automated reports that are requesting records one at a time
and might include thousands of separate requests? Or maybe they're just
accessing 30 records/hour on average each?
Connection pooling helps, but it can't compensate completely for a design
flaw.
Are you using C#.NET? If so, you might look into ADO.NET. ADO.NET is
designed to handle a lot of the management issues for you.
Thanks,
Mike C.
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>|||You have that many today but what about tomorrow? Seriously you should
always keep scalability in mind when designing the app and the schema. In
your example you are incurring about 10 times the overhead on not only the
server but the network and the client. There certainly may be times when it
might make sense to do it the A way but if you can do it the B way then you
should.
Andrew J. Kelly SQL MVP
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>
I've got a client server C# Windows Form app that communicates with a SQL
Server database backend. The number of users is less than 50.
I'm wondering what the difference in overhead is when say:
a) A Stored Procedure is called ten times, returning 1 record each time
VS.
b) The same Stored Procedure is called once returning 10 records
Due to connection pooling I would think the overhead is somewhat negligible,
especially when there's less than 50 users. I know option 'b' would be the
preffered method when it comes to database communication, but is there
really a BIG difference between the two when dealing with this few users?
Thanks!
(also posted on windows.forms newsgroup on Feb 25 with no response)Hi John,
I would think the network traffic would be significantly greater when
submiting 10 calls to get 1 record each as opposed to 1 call to get 10
records. That's automatically 10X the client-to-server network traffic.
Constantly opening and closing connections, as well as maintaining
connections that aren't in use, can use up a lot of resources on SQL Server
also. Now whether this should cause problems with a small number of users
is another story... It probably depends on how they're using the server...
Are they running automated reports that are requesting records one at a time
and might include thousands of separate requests? Or maybe they're just
accessing 30 records/hour on average each?
Connection pooling helps, but it can't compensate completely for a design
flaw.
Are you using C#.NET? If so, you might look into ADO.NET. ADO.NET is
designed to handle a lot of the management issues for you.
Thanks,
Mike C.
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>|||You have that many today but what about tomorrow? Seriously you should
always keep scalability in mind when designing the app and the schema. In
your example you are incurring about 10 times the overhead on not only the
server but the network and the client. There certainly may be times when it
might make sense to do it the A way but if you can do it the B way then you
should.
Andrew J. Kelly SQL MVP
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>
Overhead of Multiple SQL Calls
Hey folks,
I've got a client server C# Windows Form app that communicates with a SQL
Server database backend. The number of users is less than 50.
I'm wondering what the difference in overhead is when say:
a) A Stored Procedure is called ten times, returning 1 record each time
VS.
b) The same Stored Procedure is called once returning 10 records
Due to connection pooling I would think the overhead is somewhat negligible,
especially when there's less than 50 users. I know option 'b' would be the
preffered method when it comes to database communication, but is there
really a BIG difference between the two when dealing with this few users?
Thanks!
(also posted on windows.forms newsgroup on Feb 25 with no response)
Hi John,
I would think the network traffic would be significantly greater when
submiting 10 calls to get 1 record each as opposed to 1 call to get 10
records. That's automatically 10X the client-to-server network traffic.
Constantly opening and closing connections, as well as maintaining
connections that aren't in use, can use up a lot of resources on SQL Server
also. Now whether this should cause problems with a small number of users
is another story... It probably depends on how they're using the server...
Are they running automated reports that are requesting records one at a time
and might include thousands of separate requests? Or maybe they're just
accessing 30 records/hour on average each?
Connection pooling helps, but it can't compensate completely for a design
flaw.
Are you using C#.NET? If so, you might look into ADO.NET. ADO.NET is
designed to handle a lot of the management issues for you.
Thanks,
Mike C.
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>
|||You have that many today but what about tomorrow? Seriously you should
always keep scalability in mind when designing the app and the schema. In
your example you are incurring about 10 times the overhead on not only the
server but the network and the client. There certainly may be times when it
might make sense to do it the A way but if you can do it the B way then you
should.
Andrew J. Kelly SQL MVP
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>
I've got a client server C# Windows Form app that communicates with a SQL
Server database backend. The number of users is less than 50.
I'm wondering what the difference in overhead is when say:
a) A Stored Procedure is called ten times, returning 1 record each time
VS.
b) The same Stored Procedure is called once returning 10 records
Due to connection pooling I would think the overhead is somewhat negligible,
especially when there's less than 50 users. I know option 'b' would be the
preffered method when it comes to database communication, but is there
really a BIG difference between the two when dealing with this few users?
Thanks!
(also posted on windows.forms newsgroup on Feb 25 with no response)
Hi John,
I would think the network traffic would be significantly greater when
submiting 10 calls to get 1 record each as opposed to 1 call to get 10
records. That's automatically 10X the client-to-server network traffic.
Constantly opening and closing connections, as well as maintaining
connections that aren't in use, can use up a lot of resources on SQL Server
also. Now whether this should cause problems with a small number of users
is another story... It probably depends on how they're using the server...
Are they running automated reports that are requesting records one at a time
and might include thousands of separate requests? Or maybe they're just
accessing 30 records/hour on average each?
Connection pooling helps, but it can't compensate completely for a design
flaw.
Are you using C#.NET? If so, you might look into ADO.NET. ADO.NET is
designed to handle a lot of the management issues for you.
Thanks,
Mike C.
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>
|||You have that many today but what about tomorrow? Seriously you should
always keep scalability in mind when designing the app and the schema. In
your example you are incurring about 10 times the overhead on not only the
server but the network and the client. There certainly may be times when it
might make sense to do it the A way but if you can do it the B way then you
should.
Andrew J. Kelly SQL MVP
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>
Overhead for transactions
Hi all,
I am looking at the following codes. I thought that
defining the transaction is redundant. Delete by itself
IS a transaction. But what I don't know is: how much
extra overhead is added by explicitly defining the
tranaction?
Thanks in advance, Anna
--
BEGIN TRAN Del_C_ActiveBenefits
DELETE dbo.C_ActiveBenefits
WHERE ClientCode = @.ClientCode
AND PlanCode = @.PlanCode
AND ValDate = @.ValDate
If (@.@.ERROR <> 0)
BEGIN
Rollback TRAN Del_C_ActiveBenefits
--initialize Error Num from global variable
SET @.Error_Num = @.@.ERROR
-- initialize the Error description
SET @.Error_Desc = 'Error Deleting C_ActiveBenefits
Data From Target Table'
GOTO Error_Handler -- trap & handle the Error
generated
END
Commit TRAN Del_C_ActiveBenefits
--There's no extra overhead. You might end up in carrying locks over longer time, so you can
experience higher risk of blocking of course. Also, grouping several DML commands in one transaction
can increase performance as it will reduce I/O (each commit, implicit or explicit result in an I/O
to the t-log file). However, you don't want to do too many DML's in one transaction, say in a back
job, perhaps limit to 10000 DML's in one transaction.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Anna Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:063f01c3af0b$57505790$a401280a@.phx.gbl...
> Hi all,
> I am looking at the following codes. I thought that
> defining the transaction is redundant. Delete by itself
> IS a transaction. But what I don't know is: how much
> extra overhead is added by explicitly defining the
> tranaction?
> Thanks in advance, Anna
> --
> BEGIN TRAN Del_C_ActiveBenefits
> DELETE dbo.C_ActiveBenefits
> WHERE ClientCode = @.ClientCode
> AND PlanCode = @.PlanCode
> AND ValDate = @.ValDate
> If (@.@.ERROR <> 0)
> BEGIN
> Rollback TRAN Del_C_ActiveBenefits
> --initialize Error Num from global variable
> SET @.Error_Num = @.@.ERROR
> -- initialize the Error description
> SET @.Error_Desc = 'Error Deleting C_ActiveBenefits
> Data From Target Table'
> GOTO Error_Handler -- trap & handle the Error
> generated
> END
> Commit TRAN Del_C_ActiveBenefits
> --|||In addition to Tibor's comments may I suggest you review the error =handling code.
The line SET @.Error_Num =3D @.@.ERROR is after the rollback and hence =@.@.error will have been rest to zero by the rollback, so whatever is =using @.error_num is going to get zero. Safest way normally is imediately =after the statement you want to trap include:
Set @.errno =3D @.@.error,@.rcount =3D @.@.rowcount -- or similar names for =the variables
Then check @.rcount and/or @.errno for whatever you need.
Mike John
"Anna Lin" <anonymous@.discussions.microsoft.com> wrote in message =news:063f01c3af0b$57505790$a401280a@.phx.gbl...
> Hi all, > I am looking at the following codes. I thought that > defining the transaction is redundant. Delete by itself > IS a transaction. But what I don't know is: how much > extra overhead is added by explicitly defining the > tranaction?
> > Thanks in advance, Anna
> --
> BEGIN TRAN Del_C_ActiveBenefits
> DELETE dbo.C_ActiveBenefits
> WHERE ClientCode =3D @.ClientCode
> AND PlanCode =3D @.PlanCode
> AND ValDate =3D @.ValDate
> > If (@.@.ERROR <> 0)
> BEGIN > Rollback TRAN Del_C_ActiveBenefits
> --initialize Error Num from global variable
> SET @.Error_Num =3D @.@.ERROR > -- initialize the Error description > SET @.Error_Desc =3D 'Error Deleting C_ActiveBenefits > Data From Target Table'
> GOTO Error_Handler -- trap & handle the Error > generated > END > > Commit TRAN Del_C_ActiveBenefits
> --
I am looking at the following codes. I thought that
defining the transaction is redundant. Delete by itself
IS a transaction. But what I don't know is: how much
extra overhead is added by explicitly defining the
tranaction?
Thanks in advance, Anna
--
BEGIN TRAN Del_C_ActiveBenefits
DELETE dbo.C_ActiveBenefits
WHERE ClientCode = @.ClientCode
AND PlanCode = @.PlanCode
AND ValDate = @.ValDate
If (@.@.ERROR <> 0)
BEGIN
Rollback TRAN Del_C_ActiveBenefits
--initialize Error Num from global variable
SET @.Error_Num = @.@.ERROR
-- initialize the Error description
SET @.Error_Desc = 'Error Deleting C_ActiveBenefits
Data From Target Table'
GOTO Error_Handler -- trap & handle the Error
generated
END
Commit TRAN Del_C_ActiveBenefits
--There's no extra overhead. You might end up in carrying locks over longer time, so you can
experience higher risk of blocking of course. Also, grouping several DML commands in one transaction
can increase performance as it will reduce I/O (each commit, implicit or explicit result in an I/O
to the t-log file). However, you don't want to do too many DML's in one transaction, say in a back
job, perhaps limit to 10000 DML's in one transaction.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Anna Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:063f01c3af0b$57505790$a401280a@.phx.gbl...
> Hi all,
> I am looking at the following codes. I thought that
> defining the transaction is redundant. Delete by itself
> IS a transaction. But what I don't know is: how much
> extra overhead is added by explicitly defining the
> tranaction?
> Thanks in advance, Anna
> --
> BEGIN TRAN Del_C_ActiveBenefits
> DELETE dbo.C_ActiveBenefits
> WHERE ClientCode = @.ClientCode
> AND PlanCode = @.PlanCode
> AND ValDate = @.ValDate
> If (@.@.ERROR <> 0)
> BEGIN
> Rollback TRAN Del_C_ActiveBenefits
> --initialize Error Num from global variable
> SET @.Error_Num = @.@.ERROR
> -- initialize the Error description
> SET @.Error_Desc = 'Error Deleting C_ActiveBenefits
> Data From Target Table'
> GOTO Error_Handler -- trap & handle the Error
> generated
> END
> Commit TRAN Del_C_ActiveBenefits
> --|||In addition to Tibor's comments may I suggest you review the error =handling code.
The line SET @.Error_Num =3D @.@.ERROR is after the rollback and hence =@.@.error will have been rest to zero by the rollback, so whatever is =using @.error_num is going to get zero. Safest way normally is imediately =after the statement you want to trap include:
Set @.errno =3D @.@.error,@.rcount =3D @.@.rowcount -- or similar names for =the variables
Then check @.rcount and/or @.errno for whatever you need.
Mike John
"Anna Lin" <anonymous@.discussions.microsoft.com> wrote in message =news:063f01c3af0b$57505790$a401280a@.phx.gbl...
> Hi all, > I am looking at the following codes. I thought that > defining the transaction is redundant. Delete by itself > IS a transaction. But what I don't know is: how much > extra overhead is added by explicitly defining the > tranaction?
> > Thanks in advance, Anna
> --
> BEGIN TRAN Del_C_ActiveBenefits
> DELETE dbo.C_ActiveBenefits
> WHERE ClientCode =3D @.ClientCode
> AND PlanCode =3D @.PlanCode
> AND ValDate =3D @.ValDate
> > If (@.@.ERROR <> 0)
> BEGIN > Rollback TRAN Del_C_ActiveBenefits
> --initialize Error Num from global variable
> SET @.Error_Num =3D @.@.ERROR > -- initialize the Error description > SET @.Error_Desc =3D 'Error Deleting C_ActiveBenefits > Data From Target Table'
> GOTO Error_Handler -- trap & handle the Error > generated > END > > Commit TRAN Del_C_ActiveBenefits
> --
Overhead for Aliases
Hello,
I have a question, is there any performance overhead for using Aliases in a SQL query?
I mean would there be any difference in performance between the following two statements:
Select
Students.StudentID ,
Students.Name ,
Students.Code
from
Students inner join
Countries on Students.CountryID= Students.CountryID
Where
Students.IsDeleted=1
--
Select
std.StudentID ,
std.Name ,
std.Code
from
Students AS std inner join
Countries AS cntr on std.CountryID= cntr.CountryID
Where
std.IsDeleted=1
No, not as far as I have ever seen. There should be no performance impact|||Cool. But is there any explanation why? How does SQL Server treat aliases? does it affect the execution plan or something?|||What you have said is correct: an alias has no affect on the execution plan.|||I also agree with you..
But I worried about the following BLOG. When I try to execute on Mac it is really Random result..
http://weblogs.sqlteam.com/brettk/archive/2007/03/27/60141.aspx
|||No, aliases do not cause a performance issue.The blog referenced shows a difference when NOT using aliases. I would suspect, but don't know, the difference is in the compile time, not execution. The compiler has to figure out what database the field is coming from, if you don't qualify it with an alias. This takes time.
Also, the datetime variable has a maximum resolution of 3.33 ms. So a test as small as the blog describes is meaningless.
Subscribe to:
Posts (Atom)