Connection to external or internal ip? 2

[ShawnJClapper]

Hello all. I have a client that is signed up through a web service that has their sql database on a seperate computer from where the pages are hosted. We seem to be getting much slower connections than I am used to in SQL and I think the problem might be that we are connecting to SQL through the INTERNET and not the LAN. I do not know the specifics on how shared hosting services set up their network so I was wondering if anyone could help me out and see if this is the case:

The connection information they give us is out choice of either an ip address 216.XX.XXX.163 or a url. After experciencing slow connections, I tested the IP in my web browser and it brought up the sql manager site. So, I would take that as being an EXTERNAL IP? When I told the client to ask them for the INTERNAL IP, they sent him the same ip number claiming it was the INTERNAL one.

Like I said I'm not the networking expert, but since I can access that IP from my web browser wouldn't that mean it is an external ip address? And because of this, if I used that connection string, wouldn't that cause SQL to run slower since it will go through the net? I'd like some knowledge of this to make sure I am right before I coplain to anyone. Thanks for any help!

 

[mrdenny]

Yes this is an public IP. The ISP may not have private IPs setup on thier servers. Many ISPs don't bother putting the servers on private IPs and setting up NAT. They simply put all customer facing servers on public IPs.

What is the IP of the web server? If it's within the same IP space then the connection isn't going accross the internet. Odds are the database server is just slow or overloaded.

What kind of access to the SQL Server do you have? Maybe you can find out how loaded up the server is.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLBill]

Like I said I'm not the networking expert, but since I can access that IP from my web browser wouldn't that mean it is an external ip address?

Not really. I can open Internet Explorer on my computer and browse to my hard drives. I can also enter our private IPs and go to the computers on our LAN and they definitely are not accessable externally.

-SQLBill

Posting advice: FAQ481-4875

 

[ShawnJClapper]

Thanks for the information! Just wanted to get a few things straight on this before I call and tell them that is not their local IP they say it is...

Ok, the web page IP is not similar at all to the IP of the SQL server. It is like 66.165.XXX.XXX. So according to mr denny ( If it's within the same IP space then the connection isn't going accross the internet.) then we ARE accessing the ip through the internet correct?
The reason this is so important is we are loading a database with quite a bit of information. We have a .txt file with 10 million records in it that needs to be loaded into the database and there will be monthly updates of a few thousand after this initual upload. Now, the file is so large that we had to split it up in order to upload it to the server, so it's obvious that transfer over the internet will play a big part in the speed of this.
I also did some testing and noticed that when I run an ASP script that reads through the file (currently just 60,000 records) it goes through it pretty fast. However, if I just add a search query on the database during the file reading loop and don't even insert any records, it takes quite a long time to execute the script. I don't even think there is a difference between the speed of a search query or an insert query. However the more sql statements I add the slower it gets until it takes half an hour just to write 60,000 records and I think this is waaay too long because if I run the same query on my machine (granted it's 3ghz) it happens pretty darn quick.

SQLBill:
Yes, I understand typing in ip addy's of computer on my LAN will bring up stuff too, I am not on the same network as the server. I am going through a hosting company that is in some other state, but this ip still brings up the sql page.

 

[Catadmin]

Rule of thumb. Private IP addresses tend to follow these conventions: 10.0.0.0 - 10.255.255.255, 172.16.0.0 - 172.31.255.255 , and 192.168.0.0 - 192.168.255.255.

Additionally, addresses in this range are reserved for auto IP Addressing when a computer can't find it's domain controller: 169.254.0.0 - 169.254.255.255

If an address is outside of that range, it is generally considered a Public IP address. The difference between the two is that private IPs are internal to a specific network and several companies can use the same ranges listed above because their network never sees the other company's network which uses the same addressing. Public IPs are generally (but not always) used to face the internet so the public can access a person's or company's machines.

I hope that helps you out.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mrdenny]

It looks like the web server and database server are on different networks, or at different data centers. Try doing a tracert to each server and see where the traffic goes. If they follow the same path they are in the same place.

If your not sure how to read the trace routes feel free to post them.

Running a query takes much more resources than an insert statement does. I'd create a temp table and do the inserts into that table to see how long the procedure will actually take. The more queries you add into the loop the longer each rotation through the loop will take.

Does the file have to be loaded through the web server? Can't you do a bulk insert directly from the SQL Server to the web server, and load the entire file in one shot. This will put much less load on the SQL Server and web server.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[ShawnJClapper]

Yea, I haven't done a trace yet, but I did find that the IP given to FTP files is different from the one used to access the web page. The FTP IP is similar to the SQL IP so that might be in the same location.

The SQL has to be loaded through the web server as it is not a direct insert. Because of the large amount of data the database is designed to put possible duplicate entries into seperate tables to save space. The .txt file is just a series of lines with each line having a comma seperated value. However different values need to be placed into different tables, I need to sort through the file manually with a script.

 

[mrdenny]

It would be faster to load the file into a temp table, then process from within the SQL. Even with your requirment to move the duplicate entries into a table, it could probobly all be done with a few sql commands and no loops.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[ShawnJClapper]

Hmmmm. I will look into that approach to see if it helps my problem. So you are saying that I should be able to load the file with an sql statement instead of a script to a multi-column table and then use sql commands to move the information to the apporpriate places? Maybe you can help shed some light on how I would go about doing. The layout is like this:

records
recordID INT KEY AUTO_INCREMENT
uniqueID INT
userID INT

users
userID INT KEY AUTO_INCREMENT
homeID INT
locationID INT
phone TINYINT

homes
homeID INT KEY AUTO_INCREMENT
homeInfoID INT
loanID INT
saleID INT

homeInfo
homeInfoID INT KEY AUTO_INCREMENT
yearBuilt SMALLINT
totalFoot SMALLINT
bedroomNum TINYINT
bathNum TINYINT
pool TINYINT

loans
loanID INT KEY AUTO_INCREMENT
lender TINYINT
rateType VARCHAR (8)
loanType VARCHAR (8)
mortLength TINYINT

location
locationID INT KEY AUTO_INCREMENT
zip VARCHAR (9)
state CHAR(2)
city VARCHAR (50)
county VARCHAR (50)

sales
saleID INT KEY AUTO_INCREMENT
salePrice INT
saleDate SMALLDATETIME
closeDate SMALLDATETIME


Now, the record table is the largest table in the bunch and it kinda gets smaller as you go down the list there. The user table has a few duplicates that are just shown by one row and referenced from the ID for the records, since the records needs to contain a unique key for every single record in the file. The unique number in the records table (NOT THE recordID) is already made for me and is loaded from the file. The tblLocation is pretty small as there is a finite number of locations of course.
Now, problems I ran into doing the script method. First is typos. Out of 10 million records there is bound to be typos and I've already found some during the first 60,000. For instance most of the TINYINT columns are for boolean values of either a 1 or a 0. There are some intances where you could find a number like 345 in the .txt file instead so I have to put some if---then's in the asp to account for this. Is there a similar and faster way to do this through SQL? Another problem I have if there is no date then zeros are put in place of it, which I have to convert to a date to be able to insert a record in the table. As you can see this table can get complex pretty quick, and I'm not sure how I would go about doing with just within SQL..Any help appreciated!

 

[mrdenny]

You can clean your data within the temp tables before loading it into the production tables.

For the boolian issue, something like this.

update {table}
set BoolianColumn = 0 /*Or 1 if you want*/
where BoolianColumn not in (0,1)

For the dates, have the temp table data type be varchar(10). Then you can load the 0's into the field. Then run something like this to clean this column.

update {table}
set DateColumn = '1/1/1900' /*or what ever date you want, even null*/
where DateColumn = '0'

For the Records table as an example, you could do something like this to import the data.

/*records is the production table
temp_records is the table that you load the data into first*/

/*First we update the records that are already there.*/
update records
   set records.uniqueID = temp_records.uniqueID,
      records.user_ID = temp_records.UserID
from temp_records
where records.recordID = temp_records.recordID

/*Now we add the new records*/
insert into records
(recordID, uniqueID, userID)
select RecordID, uniqueID, UserID
from temp_records
where recordID not in (select recordID from records)

Now this code will need to be changed if the recordID isn't your primary key. I assumed that it was for this option.

When deciding what order to process in, you'll need to look at how the tables relate to each other. For example, Because Homes has a child relationship to HomeInfo you'll need to load HomeInfo first. The same goes for users and locations. Users has a child relationship to locations, so you'll need to process locations first, then users. Using this methodology you'll be processing sales, locations and loans first, and processing records last.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[ShawnJClapper]

Wow, that's some great coding there. I wasn't even thinking about putting a select statement after the where statement on the insert either. For instance when I put information in tblLocation, first I do a query like "where zip=<zip> and state=<state> and city=<city> and county=<count>". If I get results from the query I don't make an insert (to save room in the database) and if I don't get a result I make an insert because the record would be unique. You have made me realize how wasteful I was being. Now I just need to look into more loading comma/return character seperated tables into the temp table. Thanks for all the help and direction!

 

[mrdenny]

no problem. Look at "BULK INSERT, BULK INSERT (described)" in BOL. That's exactly what it was made for.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[ShawnJClapper]

Well, it's been awhile since I started this thread, but got pulled away to other things and finally back working on this. I'm trying to use somethink like what MrDenny suggested, however I think my sql code needs to be optomized somehow as I'm getting "Timeout expired" errors when trying to run it. This is sorting through a table with 300,000 records in it. The temporary load table is called FISData. I know it's rather messy, however I had to do that on the last couple statements at least to make sure that it was getting the correct ID's from the other tables. Any suggestions on what I'm doing wrong and how to speed this up?

'tblLocation
currentSelect="select distinct zip, state, city, county from tblFISData WHERE not exists (select zip, state, city, county from tblLocation where tblFISData.zip=tblLocation.zip and tblFISData.state=tblLocation.state and tblFISData.city=tblLocation.city and tblFISData.county=tblLocation.county)"

insertCommand.CommandText = "Insert into tblLocation (zip,state,city,county)  "&currentSelect

insertCommand.Execute()


'tblSales
currentSelect="select distinct sales_price, sales_date from tblFISData WHERE not exists (select salePrice, saleDate  from tblsales where tblFISData.sales_price=tblsales.salePrice and tblFISData.sales_date=tblsales.saleDate)"

insertCommand.CommandText = "Insert into tblsales (salePrice,saleDate)  "&currentSelect

insertCommand.Execute()


'tblLoans
currentSelect="select distinct lender, rate_type,loan_type,mortgage_years,transaction_type,mortgage_amount from tblFISData WHERE not exists (select *  from tblLoans where tblFISData.lender=tblLoans.lender and tblFISData.rate_type=tblLoans.rateType and tblFISData.loan_type=tblLoans.loanType and tblFISData.mortgage_years=tblLoans.mortLength and   tblFISData.transaction_type=tblLoans.transType and tblFISData.mortgage_amount=tblLoans.mortAmount )"

insertCommand.CommandText = "Insert into tblLoans (lender,rateType,loanType,mortLength,transType,mortAmount)  "&currentSelect

insertCommand.Execute()


'tblHomes
homeSelect = " tblFISData.lender=tblLoans.lender and tblFISData.rate_type=tblLoans.rateType and tblFISData.loan_type=tblLoans.loanType and tblFISData.mortgage_years=tblLoans.mortLength and tblFISData.transaction_type=tblLoans.transType and tblFISData.mortgage_amount=tblLoans.mortAmount and tblFISData.sales_price=tblsales.salePrice and tblFISData.sales_date=tblsales.saleDate"
currentSelect="select tblLoans.loanID, tblsales.saleID from tblLoans,tblsales,tblFISData where ("& homeSelect &") and not exists (select * from tblHomes where  tblLoans.loanID=tblHomes.loanID and tblsales.saleID=tblHomes.saleID)"

insertCommand.CommandText = "Insert into tblHomes (loanID,saleID)  "&currentSelect

insertCommand.Execute()


'tblUsers
homeSelect = "tblHomes.saleID=tblSales.saleID and tblHomes.loanID=tblLoans.loanID and tblFISData.lender=tblLoans.lender and tblFISData.rate_type=tblLoans.rateType and tblFISData.loan_type=tblLoans.loanType and tblFISData.mortgage_years=tblLoans.mortLength and tblFISData.transaction_type=tblLoans.transType and tblFISData.mortgage_amount=tblLoans.mortAmount and tblFISData.sales_price=tblsales.salePrice and tblFISData.sales_date=tblsales.saleDate"
locationSelect = "tblFISData.zip=tblLocation.zip and tblFISData.state=tblLocation.state and tblFISData.city=tblLocation.city and tblFISData.county=tblLocation.county"
currentSelect="select tblHomes.homeID, tblLocation.locationID,tblFISData.phone from tblSales,tblLoans,tblHomes,tblLocation,tblFISData WHERE "&homeSelect&" AND "&locationSelect&" And not exists (select * from tblUsers where tblUsers.homeID=tblHomes.homeID and tblUsers.locationID = tblLocation.locationID and tblUsers.phone = tblFISData.phone)"

insertCommand.CommandText = "Insert into tblUsers (homeID, locationID, phone)  "&currentSelect

insertCommand.Execute()

 

[mrdenny]

First things first. Get this code out of VB (or ASP, or C# or whatever it's in) and into a stored procedure.

That will allow the SQL Sever to cashe the execution plan.

Your problem is probably related to your indexes. Take a look at the execution plan for these queries and see where you can speed things up. Index scans are bad, full table scans are worse, while index seeks are best.

With is as a stored procedure you'll also be able to easily run the code in Query Analyzer which doesn't have the same timeout problems as ASP, VB, etc. so you can see how long it will actually take to run, not to mention make it much easier to optimize.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[ShawnJClapper]

Ok, I took your advice and learned how to do stored procedures. Here's the revised plan I came up with:

/***************************************************/

CREATE PROCEDURE sp_locationInsert AS

SET NOCOUNT ON

insert into tblLocation WITH (TABLOCKX,HOLDLOCK) (zip, state, city, county) select DISTINCT tblFISData2.zip, tblFISData2.state, tblFISData2.city, tblFISData2.county from tblFISData2 
where NOT EXISTS (SELECT zip,state,city,county from tblLocation where tblFISData2.zip=tblLocation.zip AND tblFISData2.state=tblLocation.state AND tblFISData2.city=tblLocation.city AND tblFISData2.county=tblLocation.county);

/***************************************************/

CREATE PROCEDURE sp_salesInsert AS

SET NOCOUNT ON

Insert into tblsales WITH (TABLOCKX,HOLDLOCK) (salePrice,saleDate)  select distinct sales_price, sales_date from tblFISData2
WHERE NOT EXISTS (Select salePrice, saleDate from tblsales where tblFISData2.sales_price=tblsales.salePrice and tblFISData2.sales_date=tblsales.saleDate)

/***************************************************/

CREATE PROCEDURE sp_loansInsert AS

SET NOCOUNT ON

Insert into tblLoans WITH (TABLOCKX,HOLDLOCK) (lender,rateType,loanType,mortLength,transType,mortAmount) select distinct tblFISData2.lender, tblFISData2.rate_type,tblFISData2.loan_type,tblFISData2.mortgage_years,tblFISData2.transaction_type,tblFISData2.mortgage_amount from tblFISData2 
WHERE NOT EXISTS (SELECT lender,rateType,loanType,mortLength,transType,mortAmount FROM tblLoans where  tblFISData2.lender=tblLoans.lender and tblFISData2.rate_type=tblLoans.rateType and tblFISData2.loan_type=tblLoans.loanType and tblFISData2.mortgage_years=tblLoans.mortLength and   tblFISData2.transaction_type=tblLoans.transType and tblFISData2.mortgage_amount=tblLoans.mortAmount)

/***************************************************/

CREATE PROCEDURE sp_homesInsert AS

SET NOCOUNT ON

delete from TEMPtblHomes
Insert into TEMPtblHomes WITH (TABLOCKX,HOLDLOCK) (loanID,saleID)  select DISTINCT tblLoans.loanID, tblsales.saleID from tblLoans,tblsales,tblFISData2 where tblFISData2.lender=tblLoans.lender and tblFISData2.rate_type=tblLoans.rateType and tblFISData2.loan_type=tblLoans.loanType and tblFISData2.mortgage_years=tblLoans.mortLength and tblFISData2.transaction_type=tblLoans.transType and tblFISData2.mortgage_amount=tblLoans.mortAmount and tblFISData2.sales_price=tblsales.salePrice and tblFISData2.sales_date=tblsales.saleDate

Insert into tblHomes WITH (TABLOCKX,HOLDLOCK) (loanID,saleID) select TEMPtblHomes.loanID,TEMPtblHomes.saleID from TEMPtblHomes 
WHERE NOT EXISTS (SELECT loanID,saleID FROM tblHomes WHERE TEMPtblHomes.loanID=tblHomes.loanID AND TEMPtblHomes.saleID=tblHomes.saleID);



/***************************************************/

CREATE PROCEDURE sp_usersInsert AS
SET NOCOUNT ON

delete FROM TEMPtblUsers

Insert into TEMPtblUsers WITH (TABLOCKX,HOLDLOCK) (homeID, locationID, phone)  
select DISTINCT  tblHomes.homeID, tblLocation.locationID,tblFISData2.phone from tblHomes 
INNER JOIN tblSales ON tblHomes.saleID=tblSales.saleID 
INNER JOIN tblLoans ON tblHomes.loanID=tblLoans.loanID 
INNER JOIN tblFISData2 ON tblFISData2.lender=tblLoans.lender and tblFISData2.rate_type=tblLoans.rateType and tblFISData2.loan_type=tblLoans.loanType and tblFISData2.mortgage_years=tblLoans.mortLength and tblFISData2.transaction_type=tblLoans.transType and tblFISData2.mortgage_amount=tblLoans.mortAmount and tblFISData2.sales_price=tblsales.salePrice and tblFISData2.sales_date=tblsales.saleDate 
INNER JOIN tblLocation ON tblFISData2.zip=tblLocation.zip and tblFISData2.state=tblLocation.state and tblFISData2.city=tblLocation.city and tblFISData2.county=tblLocation.county

Insert into tblUsers WITH (TABLOCKX,HOLDLOCK) (homeID, locationID, phone) 
select TEMPtblUsers.homeID,TEMPtblUsers.locationID,TEMPtblUsers.phone from TEMPtblUsers
WHERE NOT EXISTS 
(SELECT homeID,locationID,phone FROM tblUsers WHERE TEMPtblUsers.homeID=tblUsers.homeID AND TEMPtblUsers.locationID=tblUsers.locationID AND TEMPtblUsers.phone=tblUsers.phone);


/***************************************************/

CREATE PROCEDURE sp_recordsInsert AS
SET NOCOUNT ON

delete FROM TEMPtblRecords

Insert into TEMPtblRecords WITH (TABLOCKX,HOLDLOCK) (uniqueID, userID, dateID)  
select DISTINCT tblFISData2.uniqueID, tblUsers.userID, tblFISData2.dateID from tblUsers
INNER JOIN tblHomes ON tblUsers.homeID=tblHomes.homeID
INNER JOIN tblSales ON tblHomes.saleID=tblSales.saleID 
INNER JOIN tblLoans ON tblHomes.loanID=tblLoans.loanID
INNER JOIN tblFISData2 ON tblFISData2.lender=tblLoans.lender and tblFISData2.rate_type=tblLoans.rateType and tblFISData2.loan_type=tblLoans.loanType and tblFISData2.mortgage_years=tblLoans.mortLength and tblFISData2.transaction_type=tblLoans.transType and tblFISData2.mortgage_amount=tblLoans.mortAmount and tblFISData2.sales_price=tblsales.salePrice and tblFISData2.sales_date=tblsales.saleDate
INNER JOIN tblLocation ON tblUsers.locationID=tblLocation.locationID AND tblFISData2.zip=tblLocation.zip and tblFISData2.state=tblLocation.state and tblFISData2.city=tblLocation.city and tblFISData2.county=tblLocation.county

Insert into tblRecords WITH (TABLOCKX,HOLDLOCK) (uniqueID, userID, dateID)  
select DISTINCT TEMPtblRecords.uniqueID,TEMPtblRecords.userID,TEMPtblRecords.dateID from TEMPtblRecords
WHERE NOT EXISTS 
(SELECT uniqueID, userID, dateID FROM tblRecords WHERE TEMPtblRecords.uniqueID=tblRecords.uniqueID AND TEMPtblRecords.userID=tblRecords.userID AND TEMPtblRecords.dateID=tblRecords.dateID);

On the later more cpu intensive queries I went and created a couple extra tables so I could copy it over in two steps intead of one which seemed to help the timeout issues a bit. It's not timing out as much as it did before, but it still is sometimes so I suppose it needs more work. What's odd is that when it does timeout, it will no matter how high I set the timeout, but when it doesn't timeout it runs in less than a minute, sometimes only a couple seconds.

I ran some of these through query analyzer, but I'm not sure how I can use the information it gives me to improve my code. Is there some resources you can point me to that will help me on this? Thanks so much for all your help!

-Shawn