Optimization advice - Microsoft SQL Server

Andy Reynolds

I am looking for information, books, websites, etc that will help me figure
out the following things in MSSQL and Oracle:

1. Optimizing the database configuration itself.
2. Optimal table and index design.
3. Optimizing SQL statement lookups.
4. Anything else that might help to speed up our database applications.

Any advice at all would be useful.

Thanks alot.

Andy Reynolds
aj********@san.no.spam.rr.com (remove the no.spam to send email)

Jul 20 '05 #1

Subscribe Reply

1260

Jim Kennedy

"Andy Reynolds" <aj********@san.removethis.rr.removethis.com> wrote in
message news:Xt***************@twister.socal.rr.com...

I am looking for information, books, websites, etc that will help me figure out the following things in MSSQL and Oracle:

1. Optimizing the database configuration itself.
2. Optimal table and index design.
3. Optimizing SQL statement lookups.
4. Anything else that might help to speed up our database applications.

Any advice at all would be useful.

Thanks alot.

Andy Reynolds
aj********@san.no.spam.rr.com (remove the no.spam to send email)

Optimize the application.
Use bind variables
Get data efficiently, use array interface.
Avoid unnecessary parsing.
write efficient queries.

If you don't do the above then the rest is useless or of little value.
Jim

Jul 20 '05 #2

Hans Forbrich

Jim Kennedy wrote:

"Andy Reynolds" <aj********@san.removethis.rr.removethis.com> wrote in
message news:Xt***************@twister.socal.rr.com...
I am looking for information, books, websites, etc that will help me

figure
out the following things in MSSQL and Oracle:

1. Optimizing the database configuration itself.
2. Optimal table and index design.
3. Optimizing SQL statement lookups.
4. Anything else that might help to speed up our database applications.

Any advice at all would be useful.

Thanks alot.

Andy Reynolds
aj********@san.no.spam.rr.com (remove the no.spam to send email)

Optimize the application.
Use bind variables
Get data efficiently, use array interface.
Avoid unnecessary parsing.
write efficient queries.

If you don't do the above then the rest is useless or of little value.
Jim

Your list being in context of Oracle. Supporting your list ...

.... as described in Jonathan Lewis' "Practical Oracle8i" (and valuable even
now with 10g), Thomas Kyte's "Effective Oracle by Design", other books by
OakTable members (http://www.oaktable.org), and in the Oracle documents
such as "Application Developer's Guide - Fundamentals" at
http://docs.oracle.com

And adding to your list ...

Don't use DDL (creates, drops, alters) at run time
Learn Oracle's Global Temp Tables before diving into the Temp Table mill
Don't commit in loops
Don't use procedures where set operations will do
Do use stored procedures
Don't reinvent the wheel. Consider using Oracle's built-in
- workflow
- message queueing
- geospatial (Locator), Document (Text), multimedia (Intermedia)
- security (Row Level Security & Policy-based statement re-write)
- auditing
- direct http interaction (frequently replaces ASP, Perl)
- direct file, tcp interaction
- job scheduler
- external procedures
- external tables
- and so on
Don't use same development techniques for Oracle and MS SqlServer
- due to different locking internals, different techniques MUST be used!

(BTW - the comp.database.oracle.* heirarchy and the group
comp.databases.oracle are rogue - please help bring the lost souls back to
comp.databases.oracle.* per the charters copied at http://orafaq.com)

/Hans

Jul 20 '05 #3

Bob Murphy

Andy Reynolds wrote:

I am looking for information, books, websites, etc that will help me figure
out the following things in MSSQL and Oracle:

1. Optimizing the database configuration itself.
2. Optimal table and index design.
3. Optimizing SQL statement lookups.
4. Anything else that might help to speed up our database applications.

Any advice at all would be useful.

Thanks alot.

Andy Reynolds
aj********@san.no.spam.rr.com (remove the no.spam to send email)

Try http://www.oracle.com/technology//index.html for info on Oracle.
Registration is free.

Jul 20 '05 #4

Similar topics

7582

Switch Optimization

by: Mike and Jo | last post by:

I've been converting some code to C++. I'm trying to use the Switch function to compare a result. Is it possible to use switch to evaluate '>0', '<0', 0? Example switch (result) { case (>0): case (<0):

C / C++

4210

Optimization advice

by: Andy Reynolds | last post by:

I am looking for information, books, websites, etc that will help me figure out the following things in MSSQL and Oracle: 1. Optimizing the database configuration itself. 2. Optimal table and index design. 3. Optimizing SQL statement lookups. 4. Anything else that might help to speed up our database applications. Any advice at all would...

Oracle Database

4245

Optimization

by: rocky | last post by:

We're building a company wide network monitoring system in Java, and need some advice on the database design and tuning. The application will need to concurrently INSERT, DELETE, and SELECT from our EVENT table as efficiently as possible. We plan to implement an INSERT thread, a DELETE thread, and a SELECT thread within our Java program. ...

Microsoft SQL Server

1524

Seeking optimization advice

by: Dave | last post by:

Hello all, I have written an expression interpreter, and a profiler has told me that a specific part of it is in need of optimization. My purpose in posting to this newsgroup is to solicit suggestions on how I might accomplish my intended aim more efficiently. My interpreter supports the "if-then-else" construct. In addition, it...

C / C++

2379

String manipulation, optimize

by: Rune | last post by:

Is it best to use double quotes and let PHP expand variables inside strings, or is it faster to do the string manipulation yourself manually? Which is quicker? 1) $insert = 'To Be'; $sentence = "$insert or not $insert. That is the question."; or

PHP

1368

Search engine optimization tips and advice

by: qonhfmwfyrqx | last post by:

Discover how to quickly and easily create search engine optimized affiliate web sites that can make you money, even without any HTML knowledge or search engine optimization skills. Create Professional Site Maps in minutes with a revolutionary software product that not only helps to get your site indexed quicker, but also IMPROVE your search...

PHP

2373

optimization of static data initialization

by: wkaras | last post by:

I've compiled this code: const int x0 = 10; const int x1 = 20; const int x2 = 30; int x = { x2, x0, x1 }; struct Y {

C / C++

4085

C++ Code optimization

by: Amod | last post by:

hi all, Kindly suggest some tips to optimize the C++ code for higher speed. regards, Amod

C / C++

1113

I need Advice for my code's optimization....

by: sukatoa | last post by:

The code below, //I need help here.... public void processChangedLines(int offset, int length) throws BadLocationException { String text = getText(); ResetColor(); Set<String> syntax = keywords.keySet(); Color color ;Pattern p;Matcher m;

Java

2317

Zero Optimization and Sign Optimization???

by: Ravikiran | last post by:

Hi Friends, I wanted know about whatt is ment by zero optimization and sign optimization and its differences.... Thank you...

C / C++

7178

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...

Windows Server

7397

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...

C / C++

7565

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...

Online Marketing

4759

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...

C# / C Sharp

3255

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...

Networking - Hardware / Configuration

3242

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1612

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

817

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

473

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

General