Member Avatar for harriszondi

hi all, i was tasked to develop a records sytem that will store on a database probably sql server 2005. the main function of the program is to store records and be able to retrieve them to for report generation, and it should be efficient and fast when a query is thrown to the program.

basic scenario: a client or say a graduate of the school will ask for a form and what the record personnel will do is to enter the client's name and the record will appear, the record's personnel then will be able to print a report or form.

Myquestions are:

  • what techniques should i use when designing the database so when there is a search query, the program wont take a long time to produce output, this question seems to be out of the topic but im desperate. thanks.
  • will sqlserver be a good choice?
  • is it ok to use prebuild database navigation control to add, update and delete record?

ps: the records may range from hundreds of thousands to millions.

thanks in advance and i hope anyone can enlighten me with this since im only given a month to plan and code this project.

more power!

  • 4 Contributors
  • 6 Replies
  • 87 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by binoj_daniel

Dani AI

Generated

Building on and : with only a month to plan and build, the first priority is to capture the exact lookup and reporting patterns you must support (exact ID lookups, partial-name searches, multi-field filters, date ranges, paging). Design and indexes must target those patterns; otherwise optimizations will miss the real hot paths.

Practical design/tuning checklist (short, actionable)

  • Choose narrow keys and appropriate datatypes (INT is fine for millions of rows; use BIGINT only if you anticipate >2,147,483,647 rows). Keep VARCHAR lengths tight.
  • Pick a clustered index to match the most common range access (date or ID). Create nonclustered covering indexes for frequent searches and include columns used by reports to avoid lookups.
  • Avoid leading-wildcard LIKE ('%term%'); use full-text indexing for name/fuzzy searches or rewrite to prefix searches ('term%') when possible.
  • Plan for maintenance: automated statistics updates and periodic index rebuild/reorganize to prevent fragmentation.
  • For very large tables, consider partitioning by date or logical group to speed maintenance and range scans.
  • For reporting, use pre-aggregated tables, indexed views, or a reporting replica to separate OLTP and heavy read/report workloads.

One-month MVP workflow

  1. Document top 5 queries/reports.
  2. Build a normalized core schema with narrow PKs and implement parameterized stored procedures for those queries.
  3. Load representative test data, measure with execution plans and STATISTICS IO/TIME, then add targeted indexes.
  4. Add caching or indexed views for slow reports and schedule index/stat maintenance.

References: SQL Server index guidance and partitioning details are good technical reads: Index Design Guide and Partitioned Tables and Indexes.

Recommended Answers

All 6 Replies

Member Avatar for preetham.saroja

yup,,
the project which u have chosen is like-kings path to .net(for a beginner).use whatever u have mentioned
1)use sql-server.
2)use predefined functions of datagrid(insert,delete..)
3)use datagrid or gridview(And use vs-2005)....
Happy coding,,,,

Member Avatar for binoj_daniel

Well! I agree with preetham. .NET is a really good approach to start with and also to go with builtin standard controls is a better idea.

SQL can defintely handle the load of data you are expecting. Make sure you use stored procedures if possible to optimize the data handling which is a best practise to follow.

Let me know. You can read more .NET related articles here

Member Avatar for harriszondi

thanks a lot for your replies. i'll be asking for your help again folks-- im anticipating lots of problem during report generation but i can't clearly present the problem not until i start coding. peace.

Member Avatar for binoj_daniel

No issues, keep posting.

Member Avatar for IcePrincess

I have done several projects like this in my programming class, so if you need help let me know.

Member Avatar for binoj_daniel

Hi

Get paid for writing code articles on my site.

you can also read articles, ask questions and many more.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.