| Author |
Topic |
|
shireen
Starting Member
7 Posts |
 Posted - 2009-07-15 : 04:54:34
|
| i have a windows application... that uses a sql server 2005 database at the back end having 400000 records currently.....the database contains stats that are updated everyday and 15000 rows are added each day...The problem is that the application is slow.... every time a button is pressed and the records need to be searched it takes a lot of time like a minute or so.i have written querries in a separate class in my vstudio project...should i write those in sql Functions?i dont exactly know what is causing the delay... the Searching of Database or Writing that data to an Excel File...More over the Database is in one City and the Clients are in the other city... connect through a lan... the clients that are in the other city cant run the application cause it gives TIMEOUT ERROR |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-07-15 : 04:56:22
|
| Use Proper index in your Where Clause!Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-15 : 04:59:21
|
| Add indexes to the database, only return the top 100 results to start with as you don't need many more than that.Indexing will help you. The only other thing to look at is hardware, are your network cards fast enough, is your line to the other office fast enough, what spec server is this databnase on, what level RAID are you using.. etc.. |
|
|
|
shireen
Starting Member
7 Posts |
Posted - 2009-07-15 : 05:01:20
|
| i have created index on the Datetime field thats the field on which most of the searches are based on... and i have also created partitions for every month ..... in the querries many aggregate functions are used for calculation...do u think that is causing the delay? |
|
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-07-15 : 05:06:24
|
quote:
Originally posted by shireen
i have created index on the Datetime field thats the field on which most of the searches are based on... and i have also created partitions for every month ..... in the querries many aggregate functions are used for calculation...do u think that is causing the delay?
It may also cause the delay.. Better you use Stored Procedures with Temp table for queries with complex calculationSenthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-07-15 : 07:32:18
|
| Whitefang, Whitefang, wherefore art thou captain Enterprise solution?http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-15 : 15:30:16
|
quote:
Originally posted by DonAtWork
Whitefang, Whitefang, wherefore art thou captain Enterprise solution?
 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2009-07-15 : 17:45:00
|
quote:
Originally posted by shireen
i have created index on the Datetime field thats the field on which most of the searches are based on... and i have also created partitions for every month ..... in the querries many aggregate functions are used for calculation...do u think that is causing the delay?
Paritioned tables in Sql 2005 had performance issue that has been resolved in Sql 2008. All those agg function might delay.. like someone said using agg tables might help with rite indeces. |
|
|
|
shireen
Starting Member
7 Posts |
Posted - 2009-07-16 : 03:56:34
|
| More over the Database is in one City and the Clients are in the other city... connect through a lan... the clients that are in the other city cant run the application cause it gives TIMEOUT ERRORHELP! |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-16 : 04:00:41
|
quote:
Originally posted by shireen
i have created index on the Datetime field thats the field on which most of the searches are based on... and i have also created partitions for every month ..... in the querries many aggregate functions are used for calculation...do u think that is causing the delay?
By any chance in your query you have functions applied on your datetime column ? Something like convert(varchar(10), datetime_col, ???) or year(datetime_col) = 2009
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
shireen
Starting Member
7 Posts |
Posted - 2009-07-16 : 23:46:37
|
| yes i have applied! |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-16 : 23:50:15
|
Once you have any functions on the column, SQL Server will not be able to utilize any index you have on that.Post your query here. We will try to change to avoid such things.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
HOW TO SPEED UP MY Db App with 400000 records