Home Backend Development PHP Tutorial Experience in designing database_PHP tutorial

Experience in designing database_PHP tutorial

Jul 13, 2016 pm 05:01 PM
use Influence performance database Model of system design

Database design experience
Whether the design of the database model is reasonable will greatly affect the performance of the system. Based on many years of experience in designing and using databases, the author proposes the following design principles for colleagues' reference.
Use cursors with caution
Cursors provide a means of scanning line by line in a specific collection. Generally, cursors are used to traverse data line by line, and perform different operations based on different conditions for retrieving data. For cursor (large data collection) loops defined in multiple tables and large tables, it is easy for the program to enter a long wait or even crash. The author is doing the end-of-day account rollover interest calculation in the "Housing Provident Fund Management System" of a certain city. When processing a cursor with 100,000 accounts, the program entered an indefinite wait (later calculated to take 48 hours to complete) (hardware environment: Alpha/4000 128MB RAM, SCO Unix, Sybase 11.0). After modifying the program and using the UPDATE statement instead, the process was completed within 20 minutes. An example is as follows:
Declare Mycursor cursor for select count—no from COUNT 
Open Mycursor
Fetch Mycursor into @vcount—no
While (@@sqlstatus=0)
Begin
If @vcount—no=′ ′ Condition 1
Operation 1
If @vcount—no=′ ′ Condition 2
Operation 2
...
Fetch Mycursor into @vcount—no
End
 ...
Change to
Update COUNT set operation 1 for condition 1
Update COUNT set operation 2 for condition 2
...
A cursor must be used in some cases In this case, you can consider transferring the data rows that meet the conditions into a temporary table, and then define a cursor on the temporary table to operate. In this way, the performance can be significantly improved. In the background program design of the "Telecom Charging System" database in a certain city, the author performed cursor operations on a table (more than 30 qualified rows of data out of 30,000 rows) (hardware environment: PC server, PⅡ266 64MB RAM, Windows NT4.0 MS SQL Server 6.5).
An example is as follows:
Create #tmp /* Define temporary table*/
 (Field 1
 Field 2
 ...)
Insert into #tmp select * from TOTAL where condition
Declare Mycursor cursor for select * from #tmp /*Define cursor for temporary table*/
...
Tips for using index (Index)
Creating an index generally has two purposes: maintaining the index Uniqueness of indexed columns and strategies to provide fast access to data in the table. There are two types of indexes in large databases, namely cluster index and non-cluster index. A table without cluster index stores data in a heap structure, and all data is added at the end of the table; while a table with a cluster index has its data physically stored in the database. will be stored in the order of cluster index keys. A table is only allowed to have one cluster index. Therefore, according to the B-tree structure, it can be understood that adding any kind of index can improve the speed of querying by index columns, but at the same time it will slow down the insertion speed. , the performance of update and delete operations, especially when the fill factor (Fill Factor) is large. Therefore, when frequently inserting, updating, and deleting operations are performed on a table with many indexes, a smaller fill factor should be set when creating the table and index, so as to leave more free space in each data page and reduce page segmentation and re- organization's work.

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631139.htmlTechArticleDatabase design experience talks about whether the design of the database model is reasonable or not, which will greatly affect the performance of the system. Based on many years of experience in designing and using databases, the author proposes the following design principles...
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Huawei's Qiankun ADS3.0 intelligent driving system will be launched in August and will be launched on Xiangjie S9 for the first time Huawei's Qiankun ADS3.0 intelligent driving system will be launched in August and will be launched on Xiangjie S9 for the first time Jul 30, 2024 pm 02:17 PM

On July 29, at the roll-off ceremony of AITO Wenjie's 400,000th new car, Yu Chengdong, Huawei's Managing Director, Chairman of Terminal BG, and Chairman of Smart Car Solutions BU, attended and delivered a speech and announced that Wenjie series models will be launched this year In August, Huawei Qiankun ADS 3.0 version was launched, and it is planned to successively push upgrades from August to September. The Xiangjie S9, which will be released on August 6, will debut Huawei’s ADS3.0 intelligent driving system. With the assistance of lidar, Huawei Qiankun ADS3.0 version will greatly improve its intelligent driving capabilities, have end-to-end integrated capabilities, and adopt a new end-to-end architecture of GOD (general obstacle identification)/PDP (predictive decision-making and control) , providing the NCA function of smart driving from parking space to parking space, and upgrading CAS3.0

Honor Magic V3 debuts AI defocus eye protection technology: effectively alleviates the development of myopia Honor Magic V3 debuts AI defocus eye protection technology: effectively alleviates the development of myopia Jul 18, 2024 am 09:27 AM

According to news on July 12, the Honor Magic V3 series was officially released today, equipped with the new Honor Vision Soothing Oasis eye protection screen. While the screen itself has high specifications and high quality, it also pioneered the introduction of AI active eye protection technology. It is reported that the traditional way to alleviate myopia is "myopia glasses". The power of myopia glasses is evenly distributed to ensure that the central area of ​​​​sight is imaged on the retina, but the peripheral area is imaged behind the retina. The retina senses that the image is behind, promoting the eye axis direction. grow later, thereby deepening the degree. At present, one of the main ways to alleviate the development of myopia is the "defocus lens". The central area has a normal power, and the peripheral area is adjusted through optical design partitions, so that the image in the peripheral area falls in front of the retina.

No OpenAI data required, join the list of large code models! UIUC releases StarCoder-15B-Instruct No OpenAI data required, join the list of large code models! UIUC releases StarCoder-15B-Instruct Jun 13, 2024 pm 01:59 PM

At the forefront of software technology, UIUC Zhang Lingming's group, together with researchers from the BigCode organization, recently announced the StarCoder2-15B-Instruct large code model. This innovative achievement achieved a significant breakthrough in code generation tasks, successfully surpassing CodeLlama-70B-Instruct and reaching the top of the code generation performance list. The unique feature of StarCoder2-15B-Instruct is its pure self-alignment strategy. The entire training process is open, transparent, and completely autonomous and controllable. The model generates thousands of instructions via StarCoder2-15B in response to fine-tuning the StarCoder-15B base model without relying on expensive manual annotation.

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos Jul 18, 2024 am 05:48 AM

Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps

Yolov10: Detailed explanation, deployment and application all in one place! Yolov10: Detailed explanation, deployment and application all in one place! Jun 07, 2024 pm 12:05 PM

1. Introduction Over the past few years, YOLOs have become the dominant paradigm in the field of real-time object detection due to its effective balance between computational cost and detection performance. Researchers have explored YOLO's architectural design, optimization goals, data expansion strategies, etc., and have made significant progress. At the same time, relying on non-maximum suppression (NMS) for post-processing hinders end-to-end deployment of YOLO and adversely affects inference latency. In YOLOs, the design of various components lacks comprehensive and thorough inspection, resulting in significant computational redundancy and limiting the capabilities of the model. It offers suboptimal efficiency, and relatively large potential for performance improvement. In this work, the goal is to further improve the performance efficiency boundary of YOLO from both post-processing and model architecture. to this end

Honor X60i mobile phone is on sale starting from 1,399 yuan: visual quadrilateral OLED direct screen Honor X60i mobile phone is on sale starting from 1,399 yuan: visual quadrilateral OLED direct screen Jul 29, 2024 pm 08:25 PM

According to news on July 29, the Honor X60i mobile phone is officially on sale today, starting at 1,399 yuan. In terms of design, the Honor X60i mobile phone adopts a straight screen design with a hole in the center and almost unbounded ultra-narrow borders on all four sides, which greatly broadens the field of view. Honor X60i parameters Display: 6.7-inch high-definition display Battery: 5000mAh large-capacity battery Processor: Dimensity 6080 processor (TSMC 6nm, 2x2.4G A76+6×2G A55) System: MagicOS8.0 system Other features: 5G signal enhancement, smart capsule, under-screen fingerprint, dual MIC, noise reduction, knowledge Q&A, photography capabilities: rear dual camera system: 50 million pixels main camera, 2 million pixels auxiliary lens, front selfie lens: 8 million pixels, price: 8GB

Tsinghua University took over and YOLOv10 came out: the performance was greatly improved and it was on the GitHub hot list Tsinghua University took over and YOLOv10 came out: the performance was greatly improved and it was on the GitHub hot list Jun 06, 2024 pm 12:20 PM

The benchmark YOLO series of target detection systems has once again received a major upgrade. Since the release of YOLOv9 in February this year, the baton of the YOLO (YouOnlyLookOnce) series has been passed to the hands of researchers at Tsinghua University. Last weekend, the news of the launch of YOLOv10 attracted the attention of the AI ​​community. It is considered a breakthrough framework in the field of computer vision and is known for its real-time end-to-end object detection capabilities, continuing the legacy of the YOLO series by providing a powerful solution that combines efficiency and accuracy. Paper address: https://arxiv.org/pdf/2405.14458 Project address: https://github.com/THU-MIG/yo

Huawei will launch the Xuanji sensing system in the field of smart wearables, which can assess the user's emotional state based on heart rate Huawei will launch the Xuanji sensing system in the field of smart wearables, which can assess the user's emotional state based on heart rate Aug 29, 2024 pm 03:30 PM

Recently, Huawei announced that it will launch a new smart wearable product equipped with Xuanji sensing system in September, which is expected to be Huawei's latest smart watch. This new product will integrate advanced emotional health monitoring functions. The Xuanji Perception System provides users with a comprehensive health assessment with its six characteristics - accuracy, comprehensiveness, speed, flexibility, openness and scalability. The system uses a super-sensing module and optimizes the multi-channel optical path architecture technology, which greatly improves the monitoring accuracy of basic indicators such as heart rate, blood oxygen and respiration rate. In addition, the Xuanji Sensing System has also expanded the research on emotional states based on heart rate data. It is not limited to physiological indicators, but can also evaluate the user's emotional state and stress level. It supports the monitoring of more than 60 sports health indicators, covering cardiovascular, respiratory, neurological, endocrine,

See all articles