Databases
StackOverflow2013
Postsdbo
POI would like cost saving and performance options for my e-com site, moving to AWS, that uses XSLT, PHP, and SQL Server 2008r2
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POI would like cost saving and performance options for my e-com site, moving to AWS, that uses XSLT, PHP, and SQL Server 2008r2
    text
    copied!<p>A little background so as to make this clear.</p> <p>I have an e-commerce site that has several products that practically never change. I am currently keeping all the information in a SQL Server 2008 database as Rows/Columns. I am exporting on demand the product search information in response to user clicks and the product profile information in XML to be converted by XSLT backed by PHP.</p> <p>I recently signed up for the free-tier Amazon Web Services. My EC2 instance is hitting a brick wall in terms of performance and memory because of SQL Server bloat. I would like to move to MySQL but the feature set seems behind what I want to do for the needed RDBMS features I currently use. -- but that is another post on how I can get away from MS --</p> <p>My optimal scenario would be for me to minimize db usage; read, update xml directly from the filesystem or S3; provide the same services to the customer of search and buy what you want; minimize my cost while escaping associated db costs. </p> <p>I am on a limited budget, hence the free-tier usage. I am very open to any performance tips you may offer aside from what I have asked! </p> <p>My questions are:</p> <ol> <li>Namely is it crazy to not use the built-in XML parser that is part of the RDBMS?</li> <li>Is it possible to search XML using like or set conditions with PHP? I have products that have genre, product type, brand, and other metadata that the user may choose to search by OR can type a free text search. <strong>I admit I have not used PHP for XQuery tasks.</strong></li> <li>If I were to export as XML all my products, how could I keep versioned copies if I do make changes? Right at this moment, I update a log that shows which changes have been applied, and how many times the product has been viewed.</li> <li>What information should I absolutely should keep in the database? I was imagining only keeping the productid, upc, price, quantity, and enough information to link related items in the database.</li> <li>If I were able to use PHP XQuery to accomplish this, and I were to use a lookup XML file, does it matter how I structure the file in terms of performance? Say I have root->genres->sci-fi->productid/name vs. products->product[@genre,@type,@price].</li> <li>Is there an efficient way to represent related items in XML?</li> </ol> <p>Let me know if you need me to elaborate!</p> <p>Thanks</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload