SQL performance (querries or table joins)

Results 1 to 2 of 2

Thread: SQL performance (querries or table joins)

  1. #1
    Davis Guest

    Default SQL performance (querries or table joins)

    Will SQL querries of a large table run slower than a unique smaller table in asp?<BR>We have a had our databases in Access and are importing them into SQL for a website. In Access, we had two large tables organized into smaller querries.<BR>Basically the first table consists of areas throughout the country (cities, etc.) with all data about that city (including one field specifying the clients in that area) a thousand rows with a dozen columns. The second table consists of our clients and all data relating to them (including a field about what area they&#039re in) around eight thousand rows with a dozen columns.<BR>We presume to mirror our access querries in SQL and access those on the website (using only six of the columns) rather than break the larger table into smaller ones and use joins when needed to access more data.<BR>Can someone tell me if this is a flawed design and perhaps somewhere to get good design information?

  2. #2
    Reed Guest

    Default RE: SQL performance (querries or table joins)

    I asked a similar question once, and someone sent me this response (he copied and pasted it from a book):<BR><BR>Normalization<BR>The logical design of the database, including the tables and the<BR>relationships between them, is the core of an optimized relational database.<BR>A good logical database design can lay the foundation for optimal database<BR>and application performance. A poor logical database design can impair the<BR>performance of the entire system.<BR><BR>Normalizing a logical database design involves using formal methods to<BR>separate the data into multiple, related tables. A greater number of narrow<BR>tables (with fewer columns) is characteristic of a normalized database. A<BR>few wide tables (with more columns) is characteristic of an unnormalized<BR>database.<BR><BR>Reasonable normalization will often improve performance. When useful indexes<BR>are available, the Microsoft® SQL Server™ query optimizer is efficient at<BR>selecting rapid, efficient joins between tables.<BR><BR>Some of the benefits of normalization include:<BR><BR>Faster sorting and index creation.<BR>A larger number of clustered indexes. For more information, see Clustered<BR>Indexes.<BR>Narrower and more compact indexes.<BR>Fewer indexes per table, which improves the performance of INSERT, UPDATE,<BR>and DELETE statements.<BR>Fewer NULL values and less opportunity for inconsistency, which increase<BR>database compactness.<BR>As normalization increases, so will the number and complexity of joins<BR>required to retrieve data. Too many complex relational joins between too<BR>many tables can hinder performance. Reasonable normalization often includes<BR>few regularly executed queries that use joins involving more than four<BR>tables.<BR><BR>Sometimes the logical database design is already fixed and total redesign is<BR>not feasible. Even then, however, it might be possible to normalize a large<BR>table selectively into several smaller tables. If the database is accessed<BR>through stored procedures, this schema change could take place without<BR>affecting applications. If not, it might be possible to create a view that<BR>hides the schema change from the applications.<BR><BR>In relational database design theory, normalization rules identify certain<BR>attributes that must be present or absent in a well-designed database. While<BR>a complete discussion of normalization rules goes well beyond the scope of<BR>this topic, there are a few rules that can help you achieve a sound database<BR>design:<BR><BR>A table should have an identifier.<BR>The fundamental rule of database design theory is that each table should<BR>have a unique row identifier, a column or set of columns that can be used to<BR>distinguish any single record from every other record in the table. Each<BR>table should have an ID column, and no two records can share the same ID<BR>value. The column or columns serving as the unique row identifier for a<BR>table are the primary key of the table.<BR><BR>A table should store only data for a single type of entity.<BR>Attempting to store too much information in a table can prevent the<BR>efficient and reliable management of the table’s data. In the preceding<BR>example of the pubs database, the titles and publishers information is<BR>stored in two separate tables. While it is possible to have columns for both<BR>the book and its publisher’s information in the titles table, this design<BR>leads to several problems. The publisher information must be added and<BR>stored redundantly for each book that is published by a publisher. This uses<BR>extra storage space in the database. If the address for the publisher<BR>changes, the change must be made for each book. When the last book for a<BR>publisher is removed from the title table, the information for that<BR>publisher is lost.<BR><BR>The pubs database stores the information for books and publishers in the<BR>titles and publishers tables. The publisher information must be entered only<BR>once and linked to each book. When the publisher information is changed, it<BR>must be changed in only one place, and the publisher information will be<BR>there even if the publisher has no books in the database.<BR><BR>A table should avoid nullable columns.<BR>Tables can have columns defined to allow null values. A null value indicates<BR>that there is no value. While it can be useful to allow null values in<BR>isolated cases, it is best to use them sparingly because they require<BR>special handling that increases the complexity of data operations. If you<BR>have a table with several nullable columns and several of the rows have null<BR>values in the columns, you should consider placing these columns in another<BR>table linked to the primary table. Storing the data in two separate tables<BR>allows the primary table to be simple in design but able to accommodate the<BR>occasional need for storing this information.<BR><BR>A table should not have repeating values or columns.<BR>The table for an item in the database should not contain a list of values<BR>for a specific piece of information. For example, a book in the pubs<BR>database could be coauthored. If there is a column in the titles table for<BR>the name of the author, this presents a problem. One solution is to store<BR>the name of both authors in the column, but this makes it difficult to show<BR>a list of the individual authors. Another solution is to change the<BR>structure of the table to add another column for the name of the second<BR>author, but this accommodates only two authors. Yet another column must be<BR>added if a book has three authors.<BR><BR>If you find that you need to store a list of values in a single column, or<BR>if you have multiple columns for a single piece of data (au_lname1,<BR>au_lname2, and so on), you should consider placing the duplicated data in<BR>another table with a link back to the primary table. The pubs database has a<BR>table for book information and another table that stores just the ID values<BR>for the books and the IDs of the books’ authors. This design allows any<BR>number of authors for a book without modifying the definition of the table<BR>and allocates no unused storage space for books with a single author.<BR><BR><BR>Hope this helps. Take Care,<BR><BR>Reed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts