4

My query:

$query_total = mssql_query("SELECT count(*) FROM table_name",$link);

The above query gives the error log:

PHP Warning:  mssql_query() [<a href='function.mssql-query'>function.mssql-query</a>]: Query failed in ...

Count of around 10 cr is expected with above query.

When I use the same query with limit of 100 i.e:

$query_total = mssql_query("SELECT top 100 * FROM table_name",$link);

The above query runs successfully giving me the count.

I have tried:

  1. mssql_get_last_message(), but I don't capture any logs.
  2. set_time_limit(0);
  3. ini_set("max_execution_time", 0);
  4. ini_set("memory_limit","16M");

Update 1:

Even tried this,

ini_set ( 'mssql.textlimit' , '65536' );
ini_set ( 'mssql.textsize' , '65536' );

Still not working.

Please help on how can I get the heavy query executed.

Improve this question

2 Answers 2

Reset to default
5

I think I got the solution:

I increased the memory limit to 512M and sql time out to 10min and then I could get the count of around 100 milloin rows.

ini_set('memory_limit', '512M');
ini_set('mssql.timeout', 60 * 10); // 10 min
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

I don't know how much is 10cr, really, but it seems to fail in the sql server. Maybe running out of memory? What if you try count(id)? You might need to configure your sql server differently for it to work, mind you.

Improve this answer

7 Comments

tried count(id) too. It did not work. SQLServer has been already setup and query running over there it takes around 3 min of time to fetch 10 crore count
ah, google to the rescue (I had no idea what crore means), so it's 100 million rows. Well, it's a lot, still a bit surprising that the system can't deal with it. A thought: the 3 minute runtime maybe the hurdle here - the connection might simply reset before you get a response? Or does it return immediately? If it's the latter, then I'm baffled, sorry.
In fact I'd hazard a suggestion, that you might need to look for alternatives handling that much data - sql server & php may not be the best combination. If you look around, others have similar problems when they use a table that big in ms sql.
Sorry we are still in crores in India. I am actually trying to sync data into mysql database from mssql. Had created the script to fetch the data in batch of 1000 rows at a time so that the server does not overload.
How do i check if the connection is getting reset? any ideas?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.