SYM 400 Storage Indexing and Views COMPLETE

docx

School

Grand Canyon University *

*We aren’t endorsed by this school

Course

400

Subject

Computer Science

Date

Jan 9, 2024

Type

docx

Pages

6

Uploaded by PeanutButterEater102

Report
Storage, Indexing, and Views 1 SYM 400 Introduction to Database Structures Storage, Indexing, and Views
Storage, Indexing, and Views 2 1. Possible causes for slow returns -The database could contain a very large amount of data with not enough indexes created, causing a slow return to happen. There could also be an issue with not having enough memory or storage space in the SQL server. Other possibilities include a slow Wi-Fi network, a small buffer pool, and a poorly designed database schema Solutions to speed up the process -some remedies to fix a slow return on a database query include indexing (adding extra clustered and non-clustered indexes), avoiding using too many JOIN statements, sorting with a main key or character count, using the Database Engine Tuning Tool and updating or patching the hardware. Explain why implementing the index will speed queries- To implement indexes would increase the speed of queries since indexes have pointers that tell the query exactly where to look in the database instead of having to scan through every row of the table until the data is found. 2. One issue that can happen with poorly created indexes is due to the SQL server needing to go through more records than necessary to gather the data requested by the query. If too many indexes are created (over-indexing) it could eat up a lot of data causing the returns to be slower. There could be more lines than necessary in the query itself, making the request more complicated and requiring extra steps to fetch the data. 3. First I had to start this weeks’ VM, type SSMS in the start search bar, and click databases when SQL Server opens. I then create a new query under AdventureWorks2017 ant input the following query: CREATE VIEW production.RedProducts AS SELECT Production.Product.Name, Production.Product.ProductID, Production.Product.Color FROM Production.Product WHERE Color-‘Red’;
Storage, Indexing, and Views 3 Now that I have executed the query for red products it is time to test the query from the new view. I use the query: SELECT*FROM Production.RedProducts; Here we can see that it has successfully pulled up all products with the word red in them: 4. It took me a few tries to get this right, but here is the code to create the requested view: CREATE VIEW Production.RedProductsV2 AS SELECT PP.ProductID, PP.Name, PP.Color, PID.ProductModelID, DES.Description,
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Storage, Indexing, and Views 4 PDID.ProductDescriptionID, PI.Quantity, PL.LocationID FROM Production.product PP /* Production.ProductInventory AS PI, Production.ProductModel AS PID, Production.ProductDescription AS DES, Production.ProductModelProductDescriptionCulture AS PDID, Production.Location AS PL */ INNER JOIN [Production].[ProductModel] PID ONPP.[ProductModelID} =PID [ProductModelID] INNER JOIN Production.ProductInventory PI ONPI.ProductID = PP.ProductID INNER JOIN [Production].[ProductDescription] DES ONPDID.[ProductDescriptionID] =DES.[ProductDescriptionID] INNER JOIN Production.Location PL ONPI.LocationID = PL.LocationID WHERE PP.Color = ‘Red’ AND PP.FinishedGoodsFlag = ‘1’ AND PI.Quantity > 0;
Storage, Indexing, and Views 5 Now that I executed the query here is the code I use to ensure it is functioning properly: SELECT*FROM Production.RedProductsV2; The following screenshot shows that the query code pulls up the requested table:
Storage, Indexing, and Views 6
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

8288-questions-1-10.txt
CIS9350 Syllabus Spring 2023.docx
SYM 400 Topic 2 DQ1.docx
SYM 400 Tables Keys and Constraints.docx
SYM 400 Expanding the Scope of Queries COMPLETE.docx
ITT 307 Topic 3 DQ1.docx
ITT 307 Installing GUI Hash Generator.docx
ITT 370 Topic 2 DQ1.docx
Database Optimization and Performance Tuning Paper COMPLETE.docx
Term Group Project Deliverable 1.docx
workshop_9.pdf
ALU Lab.docx
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
Fundamentals of Information Systems
Computer Science
ISBN:9781337097536
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
Fundamentals of Information Systems
Computer Science
ISBN:9781337097536
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS