Posts Tagged ‘Database’

Building a high-performance platform – Key points

Posted: June 28, 2023 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , , , , ,
1

Building a high-performance platform requires careful planning, efficient architecture, and optimization techniques. Below are some key considerations and best practices to keep in mind during building a high-performance platform,

Define Performance Goals: Need to clearly define expected performance goals and metrics early on. Need to work hard on to understand the expected load, response times, and user expectations from platform.

Scalable Architecture: Design a scalable architecture that can handle increasing loads without compromising performance. Consider using distributed systems, load balancers, and caching mechanisms to distribute the workload efficiently.

Efficient Database Design: It’s a continuous process to Optimize your database design and queries to ensure fast and efficient data retrieval. Use indexing, query optimization techniques, and denormalization when appropriate.

Caching: Implement caching mechanisms to reduce the load on backend systems. Utilize in-memory caches, content delivery networks (CDNs), and caching proxies to serve frequently accessed data quickly.

Asynchronous Processing: Offload time-consuming and non-blocking tasks to asynchronous processes or background jobs. This allows the platform to handle concurrent requests efficiently and improve responsiveness.

Code Optimization: Write efficient and optimized code. Minimize resource-intensive operations, avoid unnecessary loops or recursion, and use algorithms and data structures that offer better performance.

Performance Testing: Regularly perform load testing and performance profiling to identify bottlenecks, scalability issues, and areas for improvement. Tools like JMeter or Gatling can help simulate high user loads and measure performance.

Monitoring and Logging: Implement robust monitoring and logging solutions to track the performance of your platform in real-time. Use metrics and logs to identify issues, analyze trends, and make data-driven optimizations.

Continuous Optimization: Continuously analyze performance data and user feedback to identify areas for optimization. Apply iterative improvements to enhance your platform’s performance over time.

Cloud Infrastructure: Consider leveraging cloud-based infrastructure services, such as AWS, Azure, or Google Cloud, to benefit from their scalability, reliability, and performance optimization features.

Building a high-performance platform is an ongoing process. It requires a combination of careful planning, architectural considerations, optimization techniques, and continuous monitoring and improvement.

Select rows with TIES option

Posted: January 8, 2015 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , , , , , , ,
2

Its very common when a Developer/DBA wanted to see sample of few records, commonly they use SELECT TOP N records from a table being ordered by a column,  the query look like as

SELECT TOP N [COLUMNNAME] FROM TABLENAME ORDER BY [COLUMNNAME]

As we know above statement is that if the table has multiple records having the same value as that of the selected list then all those records will not be selected. It will select only one record. But if we want to select all the rows, with same value as the one selected we have to include WITH TIES option in the query. So the query would be

SELECT TOP N [COLUMNNAME] WITH TIES FROM TABLENAME ORDER BY [COLUMNNAME]
For demonstration purpose, below are the step by step details example

Step -1 , Create a Sample Table as PRODUCTLIST

Create table ProductList(ID Int Identity(1,1),PName varchar(30),Price Decimal(10,2))

Step -2 , Insert some sample data

insert into ProductList (Pname,Price) values
(‘Bajaj CFL’,    210.00),
(‘TL’,    135.00),
(‘Table Fan’    ,450.00),
(‘Iron’    ,450.00),
(‘Cable’    ,250.00),o
(‘USB Disk’    ,450.00),
(‘Floppy’    ,120.00),
(‘CD-R’    ,280.00),
(‘CD-W’    ,450.00),
(‘USB Cable’    ,180.00)

 

For testing, SELECT TOP 3 * from PRODUCTLIST Order by Price DESC will return only 3 records as


But SELECT TOP 3 WITH TIES * from ProductList Order by Price DESC will return more than 3 records as


 If we examine about performance, initial one without TIES is more better compare to WITH TIES option, Let see an example here, suppose we have to find top 1 record having maximum price, let see the execution plan


Here we can see, WITH TIES option is performing very poorly compare to initial Select TOP query code, this happened because of ORDER BY clause, to resolve this here we have to create a proper index here for same to get the optimal performance.