Fastest way to a SQL database?

[qwert231]

K, here's the scoop. I have a webserver in our DMZ. I am currently making direct SQLClient calls to a SQL database inside my firewall. We want to know if there is any way to get a faster connection.

One suggestion here was to connect to an Access database located on the webserver that had linked tables to the SQL database.

Another was to use a system DSN (on the webserver) to make the SQL calls.

What do you guys/gals think?

 

[kaijanm]

We use a system DSN here and that works very well.

As an aside, I don't know what the overhead of a stored procedure is, but they are precompiled, so they will run queries and stuff faster. They also have the added benefit of being considerably more secure then direct calls.

There's my 2 cents!

Kimberly

 

[jfrost10]

What Kimberly said.

If you want to get a quick speed increase, switch to using stored proc's instead of straight sql calls.

Also, you could look at implementing views in your database as well. A view is a pre-determined compilation of data. For example:

A real estate database contains information on who previously owned a given house:
tblHouse information about the house
tblOwners information about owners of houses
tblPrevOwners association table holding information about previous owners of various homes

(Since tblHouse and tblOwners have a many to many relationship, tblPrevOwners is set up to handle the various combinations)

Now, to get a list of all owner names for a specific house, in sql/stored proc you'd have to write something like:
Select tblOwners.OwnerName From tblOwners Where tblOwners.OwnerID = tblPrevOwners.OwnerID
And tblPrevOwners.HouseID = [HouseID value]


Now lets create a view thats based on that sql query. You now have that snapshot of data available to you all the time, so if you wanted a listing of previous owners for a given house, you'd merely have to use:
Select OwnerName From viewOwners Where HouseID = [houseID]

Not only is your sql statement reduced, but your sql server doesn't have to go through all the processing the first statement requires because the view has already taken care of that.

Anyway, just another way you can help increase performance.

hth

D'Arcy

 

[jfrost10]

Sorry about the bolding...forgot to close my tags
doh!



D

 

[chiph]

Don't forget that the firewall (if it does stateful packet inspection) will slow things down. If you're willing to accept some risk, you can move the database into the DMZ, but on it's own private network. You'd make your web server multi-homed by adding a second network card, whose static IP address is way different from the public address. This second network would provide connectivity to the database server, but since your web server won't be forwarding packets from one network card to the other, a hacker wouldn't see it on the public internet.

Chip H.

 

[NoCoolHandle]

What about setting up a webservice or webserver based xmlrecordset?