Hello! I have two tables in an MS SQL database that I'm trying to join and display through PHP. My connection is fine and I've done it many times before, so don't worry this isn't a PHP problem. It's a my-query-won't-finish-executing problem.

Table A: (about 2 million entries)
id | lat | lon
1 | -1 | 0
2 | -5 | 1
3 | 3 | -2

etc., etc.,

Table B: (about 2,750,000 entries)
id | value
1 | 1.23
2 | 5.124

etc., etc.,

Now I really want:
id | lat | lon | value

so Ive tried a few ways of doing this, with and without joins, inner joins, all kinds of stuff. I've done something very similar with much smaller tables (about 100,000 entries) and it's worked fine. I can access either of the two tables easily, but when I want something from both the query takes forever and it doesn't finish in the 5 minutes allocated by the server.
I need the query to return much faster than over 5 minutes. (I ran a query on the server once that took many hours and still didn't finish).

So, I'm fairly new to MS SQL and most SQL in general. I'm guessing I need to index or split up the tables (even more) or maybe even remove unnecessary columns.

Anyone have any advice for this? I would really appreciate it.
Thanks!
-Ed

There are a number of areas to look at here.
1) Check the execution time of the SQL code in a query window. If the time is large look at providing indexes to speed up the query.

2) When you have optimised the SQL then check if the PHP code is still timing out. If so then have a look at the PHP.INI file. You may want to alter the MSSQL Query timeout 'mssql.timeout' to a figure larger than the default 60.

Thanks pclfw, the SQL execution time is large, so I guess I'll have to look into indexing.

I have already set the execution limit in php.ini to a huge enough number, so I don't think it's getting in the way.

It's just the SQL query execution takes so long.

Thanks again.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.