Is there a recommended size limit to an SQL Program ?

I wrote a program with 546 lines. When the code was run to line 406, it too
k
2 minutes and 9 seconds. When it ran all the way through, I stopped it afte
r
9 minutes. I ran just the section from line 406 to the end and it ran in 20
seconds.
For some reason, when all of the code is run together, the time is not the
sum of all of the individual sections. I tryed commits in various parts of
the code, but it had no effect. I then split the program to 4 sub programs
executed by a master program and the entire program ran in 41 seconds. Is
there a limit to the number of lines for one program and is there any other
solution other than splitting to subprograms ?What do you mean by a program? Are you referring to a stored procedure or a
separate application written in C# or some other development language?
Keith Kratochvil
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:1CD546BB-D5D3-4436-AB12-548F8E40E976@.microsoft.com...
>I wrote a program with 546 lines. When the code was run to line 406, it
>took
> 2 minutes and 9 seconds. When it ran all the way through, I stopped it
> after
> 9 minutes. I ran just the section from line 406 to the end and it ran in
> 20
> seconds.
> For some reason, when all of the code is run together, the time is not the
> sum of all of the individual sections. I tryed commits in various parts
> of
> the code, but it had no effect. I then split the program to 4 sub
> programs
> executed by a master program and the entire program ran in 41 seconds. Is
> there a limit to the number of lines for one program and is there any
> other
> solution other than splitting to subprograms ?
>|||Are you using a lot of temp tables in this program? Or datasets/arrays if
you are referring to a language like VB or C?
If the individual pieces use a lot of memory, and you are not properly
freeing up this memory when it is no longer needed, then things can slow
down considerably.
Per Keith's post, can you include more information, and possibly the actual
code, with notes as to where you split it into smaller pieces?
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:1CD546BB-D5D3-4436-AB12-548F8E40E976@.microsoft.com...
> I wrote a program with 546 lines. When the code was run to line 406, it
took
> 2 minutes and 9 seconds. When it ran all the way through, I stopped it
after
> 9 minutes. I ran just the section from line 406 to the end and it ran in
20
> seconds.
> For some reason, when all of the code is run together, the time is not the
> sum of all of the individual sections. I tryed commits in various parts
of
> the code, but it had no effect. I then split the program to 4 sub
programs
> executed by a master program and the entire program ran in 41 seconds. Is
> there a limit to the number of lines for one program and is there any
other
> solution other than splitting to subprograms ?
>|||Also, splitting the program up into smaller pieces requires some redefining
of inputs and outputs. Is it possible that some of your variables were
assigned incorrect values in the original code, which caused the performance
problems?
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OIfl4HFdGHA.4148@.TK2MSFTNGP05.phx.gbl...
> Are you using a lot of temp tables in this program? Or datasets/arrays if
> you are referring to a language like VB or C?
> If the individual pieces use a lot of memory, and you are not properly
> freeing up this memory when it is no longer needed, then things can slow
> down considerably.
> Per Keith's post, can you include more information, and possibly the
actual
> code, with notes as to where you split it into smaller pieces?
> "rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
> news:1CD546BB-D5D3-4436-AB12-548F8E40E976@.microsoft.com...
> took
> after
in
> 20
the
> of
> programs
Is
> other
>|||Jim, Keith,
Yes. I am using over 70 temp tables. I created a huge SQL Stored
procedure. It slows down considerably the further it runs, so I split this
SQL stored procedure into many stored procedures which I run from 1 stored
procedure with the following commands.
Exec @.iErr = u_spPCLD01
Exec @.iErr = u_spPCLD02
Exec @.iErr = u_spPCLD03
Exec @.iErr = u_spPCLD04
Exec @.iErr = u_spPCLD05
Exec @.iErr = u_spPCLD06, Etc.
I will use the drop table command afer each temp table is used. If this
does not work is there a transaction log which may need to be flushed and ca
n
such code be imbedded in the stored procedure ?
Exec
"Jim Underwood" wrote:

> Also, splitting the program up into smaller pieces requires some redefinin
g
> of inputs and outputs. Is it possible that some of your variables were
> assigned incorrect values in the original code, which caused the performan
ce
> problems?
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:OIfl4HFdGHA.4148@.TK2MSFTNGP05.phx.gbl...
> actual
> in
> the
> Is
>
>|||I'm assuming that you are passing the temp tables between procedures. If
this is the case, I would seriously consider changing the code so that it
uses real permenant tables. This will help by stopping the procedures
recompiling. Also the code will run alot faster as SQL does not need to
create the temp tables every time.
I had a stored procedure which used a single temp table. When I changed to
code to use a single table the procedure sped up by ten times! It really is
worth trying.
Regards
Colin Dawson
www.cjdawson.com
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:2106DC08-9AD7-45F2-8793-DDC9E545778D@.microsoft.com...
> Jim, Keith,
> Yes. I am using over 70 temp tables. I created a huge SQL Stored
> procedure. It slows down considerably the further it runs, so I split
> this
> SQL stored procedure into many stored procedures which I run from 1
> stored
> procedure with the following commands.
> Exec @.iErr = u_spPCLD01
> Exec @.iErr = u_spPCLD02
> Exec @.iErr = u_spPCLD03
> Exec @.iErr = u_spPCLD04
> Exec @.iErr = u_spPCLD05
> Exec @.iErr = u_spPCLD06, Etc.
> I will use the drop table command afer each temp table is used. If this
> does not work is there a transaction log which may need to be flushed and
> can
> such code be imbedded in the stored procedure ?
>
>
> Exec
> "Jim Underwood" wrote:
>

Is there a doc listing the limitations of Replication under 2005?

I am looking for a doc (BOL, etc.) which enumerates things like limits on the number of columns which can be replicated, or limits on the size of the row. BOL covers this for 2000, but I can not find it in BOL 2005.

Thanks.

Please see the following topic "Maximum Capacity Specifications for SQL Server 2005" in BOL: http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Peng

Is the SP3a the most recent MSDE distribution

I ask this question because the sp3a version I have contains a merge module -
atl.msm - which is dated (modified date) 5/14/03 and is 76KB in size whereas
the "atl.msm" that is issued with VB6 SP6 contains the same merge module but
is dated Mar 14, 2004 and is 87KB in size.
Is this simply because VB6 has issued a more recent atl? If so, when will
the MSDE be brought up to date? Or better yet, since I am distributing the
atl.msm with my setup package, can I safely replace the one in the MSDE with
the more recent version?
Regards,
Jamie
hi,
thejamie wrote:
> I ask this question because the sp3a version I have contains a merge
> module - atl.msm - which is dated (modified date) 5/14/03 and is 76KB
> in size whereas the "atl.msm" that is issued with VB6 SP6 contains
> the same merge module but is dated Mar 14, 2004 and is 87KB in size.
> Is this simply because VB6 has issued a more recent atl? If so, when
> will the MSDE be brought up to date? Or better yet, since I am
> distributing the atl.msm with my setup package, can I safely replace
> the one in the MSDE with the more recent version?
MSDERelA is based on the service pack 3a of SQL Server, which is the most
recent complete distribution...
we have to wait until sp4 is available for newer versions..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Is there any word that an sp4 is imminent? Is it waiting for the SQL Server
2005 to come out of beta?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3c4hfeF6johu1U1@.individual.net...
> hi,
> thejamie wrote:
> MSDERelA is based on the service pack 3a of SQL Server, which is the most
> recent complete distribution...
> we have to wait until sp4 is available for newer versions..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Dont think so , SP4 is announced for this or next month.
HTH, Jens Smeyer
http://www.sqlserver2005.de
"jamie" <anonymous@.nospam.somewhere.com> schrieb im Newsbeitrag
news:enT%23QkTQFHA.2132@.TK2MSFTNGP09.phx.gbl...
> Is there any word that an sp4 is imminent? Is it waiting for the SQL
> Server 2005 to come out of beta?
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3c4hfeF6johu1U1@.individual.net...
>
|||Look like it is out as of 5/6/05
"jamie" <anonymous@.nospam.somewhere.com> wrote in message
news:enT%23QkTQFHA.2132@.TK2MSFTNGP09.phx.gbl...
> Is there any word that an sp4 is imminent? Is it waiting for the SQL
> Server 2005 to come out of beta?
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3c4hfeF6johu1U1@.individual.net...
>
|||SP4 just shipped. See http://www.microsoft.com/sql/downloads/2000/sp4.asp
for details on MSDE.
joe.
"jamie" <anonymous@.nospam.somewhere.com> wrote in message
news:%23YovRe$UFHA.1148@.tk2msftngp13.phx.gbl...
> Look like it is out as of 5/6/05
> "jamie" <anonymous@.nospam.somewhere.com> wrote in message
> news:enT%23QkTQFHA.2132@.TK2MSFTNGP09.phx.gbl...
>
sql

Is the size of data.mdf too large? it is about 8G

Hi All
I have a question about size of data file.
My database have two data file :
data.mdf is 8G
log.ldf is 400M.
Is the size of data.mdf too large ?
Recently our database is often corrupted. Such as page
errors. I think the cause maybe is size of data.mdf is too
large. So it can cause the file corrupted?
Do I think right?
Last evening the database is corrupted and can not
be repaired by running DBCC command. So the only thing I
can do is restore the database from latest backup.
Is the size of data.mdf too large?
Any help is appreciate.
Thanks
Ping8GB is not large at all for sql server. If your getting corruption it is
most likely due to the hardware. Do you have a proper RAID with a battery
backed controller for the data file and is the server on a UPS?
--
Andrew J. Kelly
SQL Server MVP
"pingc@.elibrium.com" <chenpingnet@.hotmail.com> wrote in message
news:2a02b01c391e3$60679270$a601280a@.phx.gbl...
> Hi All
> I have a question about size of data file.
> My database have two data file :
> data.mdf is 8G
> log.ldf is 400M.
> Is the size of data.mdf too large ?
> Recently our database is often corrupted. Such as page
> errors. I think the cause maybe is size of data.mdf is too
> large. So it can cause the file corrupted?
> Do I think right?
> Last evening the database is corrupted and can not
> be repaired by running DBCC command. So the only thing I
> can do is restore the database from latest backup.
> Is the size of data.mdf too large?
> Any help is appreciate.
> Thanks
> Ping
>

Is SQL Server Express enough for me?

I'm trying to figure this one out. I'm currently on a shared SQL Server 2000 db. I'm almost at my limits in terms of size (300-500mb). I'm shrinking the log file constantly.

I plan on moving everything over to MySQL, but it's going to take time. I think I need a temporary solution, and I'm just not sure if SQL Server Express will be able do the trick.

Obviously, I'm under db size limit. I have no idea if the db running on 1gb ram and 1cpu is enough though, and I have no idea how to test outside of just chucking it up under real world conditions.

Anyone have any suggestions?500mb is nothing

good luck with mySQL..this site runs on it

Why are you so limited in space?|||I never wanted to run my own SQL Server, and getting some shared SQL Server space from my datacenter was a cost efficient way of doing things (plus, they handled backups, maintenance, etc).

However, my space has basically run out, my web server is starting to go (and I might be switching datacenters), and I don't think I can convert to MySQL so fast, so I'm hoping to just use Express for a few months. I realize space isn't an issue, but have zero clue about the ram/cpu limitations, or how Express runs when being used with a decently trafficked site.|||i haven't had the privilege of having a site that was that big, but wouldn't the obvious step be to move up from shared to dedicated at your same host?

that way you keep the same support team, backup procedures, etc.

if you run express, you'll be on your own as far as applying patches etc.

migrating to mysql is a definite option (the sql server guys in this forum are going to just say "shut up rudy" again, but mysql continues to blow the socks off legacy products as far as large web sites go)

however, you cannot just switch to mysql, there will be a lot more to the conversion than you think, and you are right not to do it "so fast"|||Have you considered Oracle Express, the entry-level database produced by the World's largest enterprise software company, and leading supplier of database software.|||robert, that conversion would be even costlier and longer term than the conversion to mysql

he has a high volume and very active live site

hardly entry-level|||Rudy,

I recommended Oracle based on the fact that it has superior functionality and performance with respect to concurrency, which I feel would be a key consideration for a high traffic website.

I do apologize if my comment may have been viewed as inappropriate in the context.

Regards,|||i haven't had the privilege of having a site that was that big, but wouldn't the obvious step be to move up from shared to dedicated at your same host?

that way you keep the same support team, backup procedures, etc.

if you run express, you'll be on your own as far as applying patches etc.

migrating to mysql is a definite option (the sql server guys in this forum are going to just say "shut up rudy" again, but mysql continues to blow the socks off legacy products as far as large web sites go)

however, you cannot just switch to mysql, there will be a lot more to the conversion than you think, and you are right not to do it "so fast"I'm definitely moving to mysql. It's just a matter of when. All I have are dedicated servers for my websites, but this one site needed a SQL Server db back then and I didn't need to have my own dedicated SQL Server db. For the amount of time I plan on having it stay on SQL Server, it'd probably still be a waste - plus, the moment I'm off their shared, I get none of the benefits, I'm just on my own no matter what.

I don't mind applying patches, doing backups, it's the "wtf is wrong now?" issue I don't want to run into - however, if Express will handle what I need, I might risk that for the cost of just having it on there for a month or so while I figure out my mysql transition.

The db itself is actually rather "basic". It's just standard data housing and a few simple stored procedures (at least imo). Porting to mysql shouldn't be too complex. I don't knoq mysql stored proc syntax (big issue), and then I just have to comb the site and look for errors so I can tweak any sql statements (should be minor, but could be tedious). Again, it's mostly just timing.
Have you considered Oracle Express, the entry-level database produced by the World's largest enterprise software company, and leading supplier of database software.Nope, I don't know a thing about managing an Oracle server.|||Have you considered Oracle Express, the entry-level database produced by the World's largest enterprise software company, and leading supplier of database software.

you funny

This message is too short|||Have you considered Oracle Express, the entry-level database produced by the World's largest enterprise software company, and leading supplier of database software.If you consider Oracle to be the only supplier of enterprise software, then I'll conceed that Oracle is the worlds largest supplier of Oracle software. If you look at the real world, there are more copies of both Microsoft and IBM products today in more companies around the world than Oracle could dream of publishing even if they gave their software away.

The same is true of Oracle database products

Neither of your points helps solve the problem of the original poster. Please stay on topic.

-PatP

Is possible to delete LOG file in SQL

can i delete the LOG file in the sql bcoz of large file size.

will it be able to use it without the LOG file and only with DAT file.

help me

SQL Server can't function without a log file. Regular log backups keeps your log small.

If you are not interested in the records in the log you can issue a BACKUP LOG dbname WITH TRUNCATE_ONLY. Maybe issue a DBCC SHRINKFILE (Filename, Size) WITH TRUNCATEONLY after that.

Using the SIMPLE recovery model also limits the size of the transaction log because it automatically truncates the log under certain circumstances. But a normal production database usually requires the FULL recovery model and regular backups.