Hi All
Does anyone know of a library for .Net that would allow to execute SQL
statements and scripts against SQL server and Oracle databases. The
general purpose will be to execute DDL statements, create procedures
tables etc, currently we use osql and sqlplus invocations with
parameters from .Net environment.
The ADO .Net has some limitations and I am unable to use it.
Tom.I guess it depends on what your reasons are for not being able to use
ADO.Net. I imagine that any other library would connect in a very similar
way.
"Tom" <mutobo@.interia.pl> wrote in message
news:1149579157.093610.119170@.i40g2000cwc.googlegroups.com...
> Hi All
> Does anyone know of a library for .Net that would allow to execute SQL
> statements and scripts against SQL server and Oracle databases. The
> general purpose will be to execute DDL statements, create procedures
> tables etc, currently we use osql and sqlplus invocations with
> parameters from .Net environment.
> The ADO .Net has some limitations and I am unable to use it.
> Tom.
>|||Tom
Well, you are going to use SELLECT/UPDATE/DELETE statements ONLY , I mean
not to use stored procedures.
BL passes "SELECT <> FROM Table" on DAL and it sends the statement to the
database
"Tom" <mutobo@.interia.pl> wrote in message
news:1149579157.093610.119170@.i40g2000cwc.googlegroups.com...
> Hi All
> Does anyone know of a library for .Net that would allow to execute SQL
> statements and scripts against SQL server and Oracle databases. The
> general purpose will be to execute DDL statements, create procedures
> tables etc, currently we use osql and sqlplus invocations with
> parameters from .Net environment.
> The ADO .Net has some limitations and I am unable to use it.
> Tom.
>|||Well as I said I will mainly use the library to transport DDL
statements. I would like to create procedures (oracle packages), tables
etc. This is not possible in ADO.NET due to for example script length
limitations ( 2000 chars, any table definition can be longer ) and for
oracle there is no support for ';' character as a statement
separator. I would like to take a script file that for example creates
a table with all references and successfully run it to the database ?
Tom.
Showing posts with label scripts. Show all posts
Showing posts with label scripts. Show all posts
Tuesday, March 20, 2012
Tuesday, February 14, 2012
cursors are bad
These guys I work with have some sql scripts they run over night and they bog down the server and the machine will be gummed up in the morning etc..
Well, I finally looked at this processing and the culprit is cursors. And cursors within cursors. I would like to just get some opinions about what would be more processor efficient so I can send my boss a link to this thread.
Using a cursors to pull records and update them.
vs
Create script using a scripting language that pulls the records through ADO, loops through them and performs updates as necessary using update statements and the like.
Be nice. I have to work with these guys.In my experience, T-SQL cursors are a generally bad idea. Most books I have read use the first page of the cursors chapter to say how bad cursors can be, then go on to describe how to build one.
In order to see how cursors impact performance, I have found that the best way to show people how bad they are, is to show them a perfmon with a pretty line showing CPU usage. Then run the cursor, and point out the big spike. Then ask them to imagine what happens when 4 or 5 more people run that cursor at the same time...
You should also mention that multiple application servers can be added on, but you will always be cursed with a single database machine. If the database machine is resource bound, no amount of extra application servers can help you.
As for how ADO stacks up, you may have to stage a race between a couple of processes. One T-SQL cursor based, and the other ADO based, and see who wins.
Hope this helps.|||And nested cursors are extremely bad...
The must open the first,
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
Put a dress on a cursor and you know what you have?
Anyway...You could just do it a set based method..
Got a sample of some code?|||At least in my experience, set-based solutions are at least one order of magnitude (ten times) faster than cursors in Microsoft SQL. They are often much faster than that, but the 10 times benchmark is easy to hit so that's all that I claim up front.
In every form of data processing, the closer you can keep the processing to the hardware itself, the faster the process runs. Cache is faster than disk, set based solutions on the SQL Server are faster than cursors, which can be faster than ADO under the right circumstances.
Rather than argue for ages or produce/quote meaningless benchmarks, I'd suggest you try it. Take a very simple process that affects many rows and rewrite it as either ADO, set based, or both. Compare doing the same task all three ways.
I'm confident that the set based solution will comfortably outperform any other way of solving the problem. ADO will probably come next, depending on the amount of network I/O it has to do. I'd expect the cursor to come in dead last.
I'd really like to hear your results when you get them!
-PatP|||thanks guys.
I have what looks like a few hundred .sql files. I have done set based querying before. I am not as tough as you guys but I am studying everyday.|||Can post or attach a small one so we can take a look?|||if it was my code I would but I probably should'nt because I gave the CIO a link to the thread.
95% chance they won't do anything with the advice. there is never any time or resources for anything here. they brought me into fix things and they tied my hands in the first week I was here by telling me I could not change any of the existing stuff and everything had to be a workaround.
I have told myself I have to stick it out for a year and then I would evaluate the situation. It's almost been 6 months.|||OK...
What I would do.
From a client, use Profiler to record some statistics.
Then "clone" and existing cursor process with a set based solution. Give us some "examples" that are close to what yo uhave so we can help.
Make sure the results produced are identical...write them to work tables for proof.
Publish your results.
If the results are anywhere what I expect it should be clear that it should be rewritten...it can be done a little at a time, and not everything has to be done...just find the biggest offenders...
How big is the database?
I'd dump and restore a copy of production so you can have your own dev environement...
Anyway...good luck
CIO
Constantly Ignorant Officer|||Check you calendar. It's probably only been two weeks, and just SEEMS like six months.
How are you supposed to fix anything if you can't change anything?
Do you take your car into the shop and say, "Make it run better, but don't touch the engine?"
Seems like the only thing you could recommend would be a massive upgrade to the hardware. See how they like the price-tag on that.|||Blindman,
Trust me I know. They have already talked hardware. In my last company, a few years ago when I was just a pup of a developer they beefed up the db server hardware for crappy application they were running and saw little or no gain because the code was so bad. they finally revamped the app and it was'nt until then that things got better.
I am thinking about switching auto shops. I am going to reproduce things on my local like Brett said when I get a chance. Maybe this weekend. I have app development to do today.
Well, I finally looked at this processing and the culprit is cursors. And cursors within cursors. I would like to just get some opinions about what would be more processor efficient so I can send my boss a link to this thread.
Using a cursors to pull records and update them.
vs
Create script using a scripting language that pulls the records through ADO, loops through them and performs updates as necessary using update statements and the like.
Be nice. I have to work with these guys.In my experience, T-SQL cursors are a generally bad idea. Most books I have read use the first page of the cursors chapter to say how bad cursors can be, then go on to describe how to build one.
In order to see how cursors impact performance, I have found that the best way to show people how bad they are, is to show them a perfmon with a pretty line showing CPU usage. Then run the cursor, and point out the big spike. Then ask them to imagine what happens when 4 or 5 more people run that cursor at the same time...
You should also mention that multiple application servers can be added on, but you will always be cursed with a single database machine. If the database machine is resource bound, no amount of extra application servers can help you.
As for how ADO stacks up, you may have to stage a race between a couple of processes. One T-SQL cursor based, and the other ADO based, and see who wins.
Hope this helps.|||And nested cursors are extremely bad...
The must open the first,
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
then open then next, Fetch and then close
Put a dress on a cursor and you know what you have?
Anyway...You could just do it a set based method..
Got a sample of some code?|||At least in my experience, set-based solutions are at least one order of magnitude (ten times) faster than cursors in Microsoft SQL. They are often much faster than that, but the 10 times benchmark is easy to hit so that's all that I claim up front.
In every form of data processing, the closer you can keep the processing to the hardware itself, the faster the process runs. Cache is faster than disk, set based solutions on the SQL Server are faster than cursors, which can be faster than ADO under the right circumstances.
Rather than argue for ages or produce/quote meaningless benchmarks, I'd suggest you try it. Take a very simple process that affects many rows and rewrite it as either ADO, set based, or both. Compare doing the same task all three ways.
I'm confident that the set based solution will comfortably outperform any other way of solving the problem. ADO will probably come next, depending on the amount of network I/O it has to do. I'd expect the cursor to come in dead last.
I'd really like to hear your results when you get them!
-PatP|||thanks guys.
I have what looks like a few hundred .sql files. I have done set based querying before. I am not as tough as you guys but I am studying everyday.|||Can post or attach a small one so we can take a look?|||if it was my code I would but I probably should'nt because I gave the CIO a link to the thread.
95% chance they won't do anything with the advice. there is never any time or resources for anything here. they brought me into fix things and they tied my hands in the first week I was here by telling me I could not change any of the existing stuff and everything had to be a workaround.
I have told myself I have to stick it out for a year and then I would evaluate the situation. It's almost been 6 months.|||OK...
What I would do.
From a client, use Profiler to record some statistics.
Then "clone" and existing cursor process with a set based solution. Give us some "examples" that are close to what yo uhave so we can help.
Make sure the results produced are identical...write them to work tables for proof.
Publish your results.
If the results are anywhere what I expect it should be clear that it should be rewritten...it can be done a little at a time, and not everything has to be done...just find the biggest offenders...
How big is the database?
I'd dump and restore a copy of production so you can have your own dev environement...
Anyway...good luck
CIO
Constantly Ignorant Officer|||Check you calendar. It's probably only been two weeks, and just SEEMS like six months.
How are you supposed to fix anything if you can't change anything?
Do you take your car into the shop and say, "Make it run better, but don't touch the engine?"
Seems like the only thing you could recommend would be a massive upgrade to the hardware. See how they like the price-tag on that.|||Blindman,
Trust me I know. They have already talked hardware. In my last company, a few years ago when I was just a pup of a developer they beefed up the db server hardware for crappy application they were running and saw little or no gain because the code was so bad. they finally revamped the app and it was'nt until then that things got better.
I am thinking about switching auto shops. I am going to reproduce things on my local like Brett said when I get a chance. Maybe this weekend. I have app development to do today.
Subscribe to:
Posts (Atom)