Tag Info

New answers tagged

0

Check the below from Oracle 11G R2 Compatibility document: Databases from different releases of Oracle Database software are compatible if they support the same features and those features perform the same way. When you upgrade to a new release of Oracle Database, certain new features might make your database incompatible with your earlier release. ...


0

You have redundant indexes Instead of your ALTER TABLE ALTER TABLE my_table ADD UNIQUE INDEX my_table_abcd (a, b, c, d), ADD INDEX my_table_abc (a, b, c), ADD INDEX my_table_c (c), ADD INDEX my_table_ce (c, e), ADD INDEX my_table_d (a); you only need to two of the indexes ALTER TABLE my_table ADD UNIQUE INDEX my_table_abcd ...


1

The problem is the locking in the SGA that effectively cause the the concurrency is going down. This kind of apps make the database a close to single user platform instead of a highly scalable and concurrent accessible database. The cause of this is that the connections have to be maintained in a shared memory table where inserting and deleting means locking ...


3

The index IS covering for the second query. However, it is not USEFUL to support the seek. Because neither bar_id nor date_sent is leading the index, the optimiser cannot seek for it. What you have achieved with the covering index is to make table scan faster for the second query. But you have not supported a better seek strategy. This covering index would ...


1

Likely, the issue is caused by the very long IN lists. Instead of using the IN list, create a temporary table with the values you want to filter on. Then rewrite the query as a join: CREATE TABLE #FilterOutputArea (...) INSERT #FilterOutputArea VALUES ('E00139237') ... etc... CREATE TABLE #FilterColumnID () INSERT #ColumnID VALUES (298) ... etc... SELECT ...


1

I really don't think you have anything to worry about here. Unless you mean a completely separate SSD + controller for every individual file, there are always going to be competes for concurrent read/write activity. Even then I suspect the OS can't write to 20 different files at the same time without any of them waiting on each other to some minor degree. ...


0

You should refactor the query as follows SELECT B.ShipAddress1, B.CustomLabel, B.Quantity, B.PhService, B.UniqueID, B.FullName, B.ShipPostCode FROM FROM ( SELECT ShipAddress1 FROM internetorders WHERE Complete = '0' AND LabelCreated = '0' GROUP BY ShipAddress1 HAVING Count(*) > 1 ) A LEFT JOIN internetorders B USING ...


1

If it's stored procedure, perhaps PIVOT tblConsolidadoJob table and insert results into hash table and then use very same hash table on a left join? Something like this: SELECT * INTO #temporary FROM ( SELECT CodJob, Quant, TipoCons, Perc FROM tblConsolidadoJob ) AS SourceTable PIVOT ( MAX(Perc) FOR TipoCoins IN ...


0

Assuming that you are prepared to compromise on the absolute correctness of the answer in order to obtain practical performance, you could do the following: Create an index on score (assuming descending, but either way is workable) Create a maintenance job to rebuild this index periodically (how often depends on your willingness to have your rank results ...


8

From a DBA perspective, the key difference between OLAP and OLTP is the tuning method you apply to the queries. The read/write ratio doesn't really tell you anything useful. I have a little "magic quadrant" that I use to illustrate the difference (in your case, consider BI/DW and ETL the same as OLAP): Basically, if you must touch a lot of data to ...


0

But, I myself checked the Delete and Insert vs Update on a table that has 30million (3crore) records. This table has one clustered unique composite key and 3 Nonclustered keys. For Delete & Insert, it took 9 min. For Update it took 55 min. There is only one column that was updated in each row. So, I request you people to not guess. The equations will ...


-5

Since the database is created in a normal SQL Server instance, so it's an OLTP database as OLAP databases are created in Analysis Server instances only. Moreover, you cannot insert, update or delete from OLAP databases, so as a software developer you don't have the option to use OLAP as the core database for your solution. Not only that, you cannot query ...


15

No. No gain at all. The manual explicitly states: Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database ...


2

We cracked this a while back by replacing the RAID controller on the server. The disks and server configuration were fine but it appears that the RAID controller couldn't deal with the IO. We are now in the good place of reads ~ 2ms and Writes at <= 5ms


0

The duration of the creation of the index will be directly related to the size of the index. I don't think it would be wildly different for the 2 options provided. Under the hood MySQL is going to copy the table to recreate it in the new structure, so consider the amount of time MySQL will take to read the data and write the data. The process is single ...


0

Inserting records in random order won't yield optimal write performance. You can test sorting your set by primary key before inserting or just generating them in an increasing order (e.g. storing the last value in a var and increasing it by a random number).


5

Yes, it should be fine. You should use connection pooling though, as pg uses a fair amount of memory per connection (about 10MB AFAIK). Your SaaS application should connect as one user, then set role to the real user. This allows you to use connection pooling, as the connection string will be the same, but use different users. You should reset role when ...


1

Performance : thousands concurrent connection will eat up Your memory, approximately a value above 1,000 concurrent connections advised to use connection pooling, pgbouncer is a good one, developed by skype. Administering : Administer 50,000 users will be a big job IMO. How about differentiate costumer with same data access using different ...


8

when does SQL Server notice the new indexes and determines whether they actually help the query? Immediately. Creating an index causes a recompile because of a schema change. Here's an illustration of this. First to setup the objects in the test database: use TestDatabase; go if exists (select 1 from sys.tables where object_id = ...


1

My experience is that a useful index gets used almost immediately. It does change the information available to the optimizer after all. However, a new index may never get used if the optimizer rejects its value. You should note that a stored procedure may hold onto an existing plan for some time. If so, you can use exec sp_recompile procedurename to ...


1

If the UUIDs are not the sequential variant, then inserting them in the order they are created will cause some random leaf node in the index to be dirtied for each row inserted. Once the index is large enough, this will kill write performance on spinning hard drives, as it won't be able to consolidate the writes for efficient writing to disk.


4

Add a second column which will hold a hash of the text value. Create the index on the hash. Even if there is a hash collision there will be only a few rows to read and perform a full comparison on the text vlaues.


0

I made up a perl script that load index cache for myisam table and full table for InnoDB including InnoDB secondary index. Feel free to use it. https://github.com/benjaminlin/mysql_cache_prewarm :)


0

Here's a stab at this: see fiddle at http://sqlfiddle.com/#!9/1fc60/1. My algorithm in general to take all the start/end values for all clients, sort those numbers and make each adjacent pair of values into an interval. For n clients, you have 2n-1 intervals (in worst case). Then, evaluate each of those intervals to see how many clients' IP ranges they ...


0

Basically we would like to create a TRIGGER for each table we want to be notified for an UPDATE/INSERT/DELETE operation. Once this trigger fires it will execute a function that will simply append a new row (encoding the event) to a log table that we will then poll from an external service. That's a pretty standard use for a trigger. Before going all ...


0

With GeomFromText or any other *FromText function you can specify the SRID. I don't think you can do it otherwise. PointFromText('POINT(lat lng)', 4326)


1

Confusing, right? Well, to actually get the ratio, you'll need to do it yourself using the Buffer cache hit ratio base in addition to the Buffer cache hit ratio by taking the result from Buffer cache hit ratio / Buffer cache hit ratio base. Try the below query (from Less Than Dot), which should give you the % you're looking for: SELECT (a.cntr_value * 1.0 ...


4

To get to the bottom of this you likely need to identify the SQL statements that are generating the REDO. Unfortunately I don't think there is any simple, completely automated way to detect REDO per SQL. But it's usually not too difficult to track down the worst SQL statement. Since you've already got AWR, look at the SQL Statistics section, ordered by ...


2

If indexes are in place: updates in indexes are always logged. An other thing is: nologging is only done for statements that copy data. insert into x values (y,z) will always be logged, insert into x select y,z from source can be unlogged.


1

Find a small recordset and see how many loops your code does. How many does it do for 10, 100 and 1,000 records? You will see that the number of loops increases exponentially as the number of rows increases. My rough guess is that you have asymptotically big O(2^n+2^n+2^n). Asymptotics is a way of estimating work where the amount of rows processed is not ...


0

On tables with a large number of rows being inserted having a ever-increasing clustered index generally improves write performance as it ensures that new records are added together "at the end" of the table. (Note that clustered indexes and primary keys are not the same thing, you can actually have a different primary key from your clustered index. Its the ...


2

Conclusion and a workaround After exhausting all options on Windows, I decided to switch to Linux, mostly because I was frustrated with inability to profile and debug in detail. I have moved the whole setup to Ubuntu 14.04. I first tried XAMPP but gave up because of conflicts between XAMPP and MySQL and MySQL Workbench. Then I moved to vanilla MySQL (5.5, ...


6

Table Partitioning, like several other features, is quite often (or possibly even most often?) used inappropriately. Any of the cautions I would give have been nicely stated in @swasheck's answer. In addition, an alternative to consider is Partitioned Views. This is a way of keeping fully separate tables but linking them together via UNION ALL in a View. ...


7

Before deciding how large you want the partition to be, please consider the query plan implications of partitioning. From a purely performance perspective, partitions serve as a form of coarse grained index. This can provide extra performance, but it is also a source of performance regressions, especially if the partition key does not appear in all queries. ...


6

I'm going to take a different approach and note that partitioning (in SQL Server) is primarily a data management feature with query performance being a possible secondary outcome, depending on how you manage it.1 As noted in the linked article, the primary benefit of partitioning is that you can quickly move data by using partition switching. For example, ...


9

There's a reason that the general advice is that it depends on the table design and the queries on it. My answer to your other post on Stack Exchange says as much. Saying "queries which are simple count(*) operations based on an indexed ID field" doesn't give much information since it says nothing of the cardinality of the set of rows under consideration. ...


2

A request that is being blocked does no work. Here's an example: Create a blocking query use AdventureWorks2012; go begin tran; update HumanResources.Department set Name = 'testing 1 2 3' where DepartmentID = 7; --rollback tran; Run a query that will be blocked by the above session use AdventureWorks2012; go select * from ...


4

...it runs more than 5 minutes which I had to cancel and it is showing PAGEIOLATCH_SH wait type The PAGEIOLATCH_SH wait occurs when SQL Server is waiting for a data or index page that is not in memory to be fetched from persistent storage. The sys.dm_db_index_physical_stats dynamic management function running in LIMITED mode (the default) needs to read ...


1

There's some information missing that would be useful but my first approach would be to concatenate the files into a smaller number of large input files to cut down the file handling overhead. Look at the load method and check if the data profile supports the use of SQL Loader direct loads. If SQL loader is not appropriate maybe use external tables and add ...


0

any particular reason you are using raid 6? raid 6 has extra overhead in write operations as it has additional parity over raid 5. Also the number of indices on the table can potentially slow down inserts. So to answer your specific question: 'Can I do something to improve speed?' Yes, move away from raid6 (to raid 1+0 or even try ASM with raw ...


3

The disk activity is high because it needs to pull your whole database into RAM to do its analysis. If you call sys.dm_db_index_physical_stats with fewer NULLs, it will be able to run your query on a subsection of the database, which will then run much quicker. Sadly, your TOP 1 isn't stopping it from doing all the calculations, as you're calling the main ...



Top 50 recent answers are included