TD6.1 to Sql Server via ODBC on a VPN

[ianrudge]

I have managed to get this to work but the speed is appalling - what other options are there?

Strangely it is slower if i try to reduce this by limiting query to say emp_id = :Emp_id rather than get them all with no limits.

[Jeff Luther]

Strangely it is slower if i try to reduce this by limiting query to say emp_id = :Emp_id rather than get them all with no limits.

Gee, that sounds like an index (or lack thereof) issue to me, Ian.

emp_id = :Emp_id

Also try this: "SELECT.... WHERE emp_id = " || SalNumberToStrX( Emp_id, 0 )"
and see if that makes a difference.

My basic SQL test for <some TD issue> is to try it in SQLTalk (with hard-coded ID) and see what you get. You even get a 'free timer': set time on;

Out of curiosity what's on the VPN? TD or MS SS? And what DB version?

[ianrudge]

Definitely not indexing as its a primary key on non vpn its a 1ms return on 10000 record table its a site to site vpn using watchguard firewalls

[ianrudge]

Did some testing td 6.1 sp1 deploy on client 50mb connection with the exe local. SS 2008 on server on 100mb connection

This where string "employee.emp_id = taxytd.emp_id and paysecure.payroll_id = employee.payroll_id
and pcompany.company_id = payroll.company_id and taxytd.company_id = employee.company_id
and paysecure.user_id = 'IAN' and payroll.payroll_id = employee.payroll_id
and taxytd.yr = 2012 and employee.dept_id = depts.dept_id and employee.emp_id = train_record.emp_id " takes 40seconds which is bad enough (on lan less than 1sec)

this one
" Where (employee.emp_id = taxytd.emp_id and paysecure.payroll_id = employee.payroll_id
and pcompany.company_id = payroll.company_id and taxytd.company_id = employee.company_id
and paysecure.user_id = 'IAN' and payroll.payroll_id = employee.payroll_id
and taxytd.yr = 2012 and employee.dept_id = depts.dept_id and employee.emp_id = train_record.emp_id ) and employee.emp_id = 10060" takes an extra minute!!!

[Jeff Luther]

Well, let's see... Looks like a 7-way join if my counting still works Friday late afternoon. Plus 2 or 3 equi-joins with values.

I'm big on experimenting to find out what if any 'edges' there are to the problem. I'd:
* move the emp_id = 10060 to the front and see if that makes a difference

* remove parens. and try that. joins are all ANDs.

* just for fun add parens. to that first string.

I wonder if the DB is creating a temp. table? In SQLBase you can turn on Execution Plan to see what the optimizer is doing. Don't know if such a thing exists in MS SS.

[ianrudge]

I know the number of joins too However I am looking at the comparison between the LAN and a VPN. On the LAN the results return within 1sec on the VPN it is over 1minute 40 sec