-2

How a to increase the perfomance of a SQL Statement (select * from table where param1=123,param2=345 ) .Suppose a table having more than 500,000 rows of data.

Its a DBF to SQL migration application. All data are migrated to SQL now . while Connecting to new SQL DB its taking more time in first time page load and on the second time page load it is less time

FYI : there is no join between tables , thats why using select * from commands and its a string query executing from VFP. Only Database is changed from DBF to SQL Server 2016.

Things I have done so far: 1. Added non clustered index columns based on the where clause param 2. checked the execution plan.

Sreejesh Babu
  • 59
  • 7
  • 1
    This is really a very broad and generic question, and impossible to give you an objective answer. You've provided no schema. You've provided no data. You've only hinted at a generic query (`select *`) and said that it's the only thing you can do (and I can't imagine why you'd have such a restriction). But also: we don't know what your performance issue is. And lastly: we don't even know what *database* you're using. Please edit your question to be more specific. – David Makogon Mar 06 '18 at 11:19
  • Will you be more specific with your question? – Nadun Kulatunge Mar 06 '18 at 11:22
  • Which dbms are you using? – jarlh Mar 06 '18 at 11:31
  • What's a _lakh_? – jarlh Mar 06 '18 at 11:32
  • Execute `select * from Hugetable` and go for lunch to receive a relieving letter from the company. There are lot of considerations to be added in your question – Ven Mar 06 '18 at 11:52
  • Hi guys this not as easy as you think , Its an old vfp application migrating to SQL db and they are used select * from tablename statement for opening a table data.We cant change anything on that statement, But we can set non clustered indexes on columns. Some tables having 500,000 rows . taking 30 to 35 seconds first time page load and second time its loading in 8 to 12 seconds . what I want to know is how we can improve the time in the first time page load ? – Sreejesh Babu Mar 06 '18 at 12:11
  • checking the plan guide and SQL cache now .. Am I in a correct path or not ? – Sreejesh Babu Mar 06 '18 at 12:12
  • whether plan guide help to improve the perfomance ? it is loading much faster in second time why ? – Sreejesh Babu Mar 06 '18 at 12:16
  • I am passing only two parameters along with select * from statement example : select * form tablename where param1=11, param2=222 – Sreejesh Babu Mar 06 '18 at 12:17
  • Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product (every relational databases is a "SQL database") But if you need to retrieve all rows from a table no index or other structural option will help. The performance is limited by your hardware. Depending on the DBMS product this could be improved by exploiting parallel query capabilities. –  Mar 06 '18 at 12:28
  • Your actual problem is the first time load? – Kostis Mar 06 '18 at 14:38

1 Answers1

0

If you've added and index on (param1,param2,id) and the execution plan is using the index there's not much more you can do. If select * is not using the index then add an ORDER BY.

The first time performance is taking the overhead of connecting to the database. Are you using connection pooling? Is this a batch job?

How long is it taking and how long do you expect it to take?

McMurphy
  • 1,235
  • 1
  • 15
  • 39