locked
How can i write faster sql RRS feed

  • Question

  • User1988569847 posted

    I use postgres database for my asp.net project. Day by day my database in incresing , its running last 1.5 year in live.

    My all sql works fine. But day by day database is incresing and my sql run very slow .

    Is there any solution for this . Please suggest me.

    Tuesday, December 13, 2011 11:04 PM

Answers

All replies

  • User1983249378 posted

    There are several ways to fasten the data retrieval

    You can work on two aspects

    1. At your code level retrieval

    2. At you database level retrieval

    Code Level Techniques

    Joins , un-necssary inner joins , Subqueries and inner subqueries are performance killers so watch out where ever possible remove those.

    When retrieving data dont load complete data , but do pagination let say you have 1million records then just retrive their count and first 1000 records when click on page 2 go for next 1000 and so on.

    For data fetching and loading use the fastest possible data loading technique like JSON , xmlthttprequest or xml

    Database Level Techniques

    Try to make flat tables , mean avoid un-necessary Normalization

    Use indexes 

    Always use stored procedures instead of writing code level commands

    Above are few suggestions 

    Tuesday, December 13, 2011 11:17 PM
  • User1988569847 posted

    data fetching and loading use the fastest possible data loading technique like JSON , xmlthttprequest or xml

    Any example please.

    Tuesday, December 13, 2011 11:25 PM
  • User1983249378 posted

    using Jquery

    $.getJSON("GetUser.aspx");

    Make a simple page where you built your simplest json response from sql server data like

    there are some third party json searilizing dlls that write json one of them is

    one you can find here 

    http://json.codeplex.com/

    from this dll you built json response of user

    the user json object will store all information regarding user

    then using this user json object you can fill your html at client side super fast and you can play it with.

    here are some understanding

    http://www.simple-talk.com/sql/t-sql-programming/json-and-other-data-serialization-languages/

    http://williamsportwebdeveloper.com/cgi/wp/?p=494 

    Here is already discussed thread

    http://forums.asp.net/t/1656437.aspx/1?Speed+up+data+retrieval

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 14, 2011 12:30 AM
  • User-1407477457 posted

    In addition to the other suggestions,

    select just the fields you need.  ie do not use select *

    avoid functions in your where clause.  for example, instead of

    where to_char(someDateField, 'yyyy-mm') = '2011-12'

    use

    where someDateField >= {d '2011-12-01'

    and someDateField < {d '2012-01-01'}

    join tables in the from clause, not the where clause.

    put as many constraints as possible in the from clause instead of the where clause.

    use query parameters for all variables

    Tuesday, December 20, 2011 9:33 PM